Convertir un campo VARCHAR a tipo DATE con SQL en MySQL

Es muy común, por lo menos con mi tipo de suerte, entrar y ver en una base de datos, que han puesto fechas en VARCHAR (cadena de texto). Y sin formato real de fecha!

El otro día me toco una base de datos como 1.6 millones de registros, y el formato DD-MM-YYYY (hh:ii:ss) varchar (40). ¡No tengo idea porque así!

¿Que debía hacer? ¿Usar PHP? Ja! Una base de datos de 1.6 millones de registros me hubiera tomado horas procesarla y no hacer nada con el server.

Es por eso que SQL puro en estos casos es la salvación.

Prepará una copia de la base de datos

Cualquier DBA prudente debe saber que NUNCA HAGAS COSAS EN LA BASE DE DATOS DE PRODUCCIÓN. JAMÁS!. No importa que, ni lo más simple.

Primero debemos hacer los siguientes pasos:

• Copiamos la tabla que haremos la operación
  
• Creamos una nueva columna date en nuestra DB (No hagan las operaciones directas sobre la columna que tiene los datos o se equivocan y volverán a copiar la tabla completa)
  
• Comenten la tabla de pruebas, así si alguien entra, saben están realizando una tabla temporal (De hecho, llamenla temp_lo_que_sea)

La solución a las fechas con SQL

Bien ahora tenemos muchos registros, y debemos formatearlos a YYYY-MM-DD, formato date normal en MySQL.

En PHP lo que haríamos es traer el registro, aplicar substr y luego concatenar los resultados (".").

Algo como:

Código :

$date = $row["date"];
$date = substr($date,0,4)."-"substr($date,2,2)."-".substr($date,6,2);

Luego un UPDATE a la tabla en cuestión ¿Verdad?

Con SQL podemos hacerlo directamente usando CONTACT Y SUBSTRING. Son funciones natales de SQL, así que podrán hacer uso directamente desde la consola, o cualquier cosa que ejecute SQL en realidad.

Así que lo que tenemos que hacer es lo mismo:

Código :

UPDATE table_name SET new_date = CONCAT(SUBSTRING(date,1,4),"-",SUBSTRING(date,3,2),"-",SUBSTRING(date,7,2))

Noten que no he limitado ni puesto WHERE, esto es porque editaré la tabla completa.

Función CONCAT de SQL

Esta función es muy fácil, concatena de la siguiente forma:

Código :

CONCAT(VALUE,VALUE2,VALUE3,VALUE4)

Por eso fijense que hicimos:

Código :

CONCAT(SUBSTRING(date,1,4),"-"...

Eso implica el resultado del SUBSTRING y luego el "-".

Función SUBSTRING de SQL

La función SUBSTRING se comporta como siempre, solo que empieza a contar desde 1, don´t know why.

Lo que hará esta función es extraer un valor usando la siguiente estructura:

Código :

SUBSTRING(VALUE,START,COUNT);

Es por ello que si queremos cortar los dos primeros caracteres debemos hacer:

Código :

SUBSTRING(date,1,2);

Implementación

Una vez estamos seguros de la sentencia SQL, hagan una copia fiel de la tabla, y ejecuten en la tabla de producción la sentencia SQL. De esta forma, ahora, tendrán el resultado esperado.

¿Sabes SQL? ¿No-SQL? Aprende MySQL, PostgreSQL, MongoDB, Redis y más con el Curso Profesional de Bases de Datos que empieza el martes, en vivo.