Cuando una fecha llega como cadena en un formato que no es ISO, un simple cast '15/03/2024'::date o falla o la interpreta de forma distinta a la que esperas. La funcion TO_DATE analiza la cadena con una plantilla explicita que escribes tu mismo, y eso hace que el resultado sea predecible. Aparece sobre todo al importar CSV, en migraciones y al analizar volcados manuales, donde el orden de dia, mes y ano se conoce de antemano y conviene fijarlo en la propia consulta.
Sintaxis basica
TO_DATE recibe dos argumentos: la cadena de entrada y una plantilla de formato que describe como esta compuesta esa cadena.
SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD') AS d;
La plantilla se construye con codigos de campo: YYYY es un ano de cuatro digitos, MM el mes, DD el dia, Mon el nombre corto del mes y Month el completo. Los literales como guiones y barras se escriben tal cual:
SELECT TO_DATE('15/03/2024', 'DD/MM/YYYY') AS eu_style,
TO_DATE('March 15, 2024', 'Month DD, YYYY') AS verbose,
TO_DATE('20240315', 'YYYYMMDD') AS compact;
Por que no usar solo ::date
El cast ::date depende del ajuste datestyle del servidor y entiende sobre todo cadenas parecidas a ISO. Para formatos ambiguos resulta peligroso:
SELECT '03/04/2024'::date;
SELECT TO_DATE('03/04/2024', 'MM/DD/YYYY') AS as_us;
SELECT TO_DATE('03/04/2024', 'DD/MM/YYYY') AS as_eu;
Con TO_DATE la intencion queda codificada en la propia consulta y no depende de la configuracion del servidor. Un caso practico es cargar fechas de nacimiento de usuarios que llegaron en formato europeo:
SELECT id, email, TO_DATE(raw_signup, 'DD.MM.YYYY') AS signup_day
FROM users_staging;
Ventajas de una plantilla explicita:
- el resultado no depende del
datestyle ni de la configuracion regional del servidor;
- la consulta se autodocumenta: se ve que formato se espera;
- funciona igual para diseno US, EU o cualquier formato personalizado.
La trampa principal: los campos fuera de rango dependen de la version
El detalle clave de TO_DATE es que su rigor depende de la version de PostgreSQL, y ahi es justo donde se rompen las migraciones. Los comentarios de abajo muestran el comportamiento antiguo anterior a PostgreSQL 16: el mes 13 y el dia 32 no provocaban un error, sino que se «desbordaban» hacia delante (el mes 13 pasaba a enero del ano siguiente y el dia 32 al primero del mes siguiente). Esa promocion silenciosa de una fecha invalida en una valida era la causa clasica de errores dificiles de encontrar.
SELECT TO_DATE('2024-13-01', 'YYYY-MM-DD');
SELECT TO_DATE('2024-01-32', 'YYYY-MM-DD');
SELECT TO_DATE('2024/03/15', 'YYYY-MM-DD');
A partir de PostgreSQL 16 el comportamiento cambio: las dos primeras consultas ya no desbordan el exceso, sino que lanzan un error: date/time field value out of range para el mes 13 y date out of range para el dia 32. Los resultados 2025-01-01 y 2024-02-01 de los comentarios solo aparecen en PostgreSQL 15 y anteriores. La tercera consulta, en cambio, es permisiva en cualquier version: el desajuste de separadores entre datos y plantilla (/ frente a -) sigue pasando sin quejas. Asi que, si necesitas rigor garantizado en cualquier version, no confies en la validacion de rangos integrada: comprueba el resultado formateandolo de vuelta con TO_CHAR:
SELECT raw_signup
FROM users_staging
WHERE TO_CHAR(TO_DATE(raw_signup, 'YYYY-MM-DD'), 'YYYY-MM-DD') <> raw_signup;
Anos de dos digitos
El codigo YY activa una regla de ventana: PostgreSQL completa un ano de dos digitos en relacion con el actual, lo que casi nunca es lo que quieres para fechas historicas.
SELECT TO_DATE('15-03-49', 'DD-MM-YY');
SELECT TO_DATE('15-03-99', 'DD-MM-YY');
SELECT TO_DATE('15-03-1999', 'DD-MM-YYYY');
Consejo: exige YYYY siempre que puedas y normaliza los datos de origen a anos de cuatro digitos para eliminar por completo la adivinanza.
TO_DATE frente a TO_TIMESTAMP
TO_DATE descarta la parte de la hora: el resultado es siempre de tipo date. Si la cadena tiene horas, minutos y segundos, usa TO_TIMESTAMP, de lo contrario perderas precision en silencio.
SELECT TO_DATE('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI');
Diferencias entre motores:
- MySQL no tiene
TO_DATE; usa STR_TO_DATE('15/03/2024', '%d/%m/%Y') con codigos de porcentaje, y en modo estricto devuelve NULL ante una fecha invalida.
- ClickHouse ofrece
parseDateTimeBestEffort y una parseDateTime estricta con plantilla al estilo Java; usa el sufijo OrNull por seguridad.
SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y') AS d;
Al trasladar el analisis de fechas entre PostgreSQL, MySQL y ClickHouse, el verdadero peligro no es la sintaxis de TO_DATE, sino la diferencia en como trata cada motor los valores invalidos. PostgreSQL anterior al 16 desborda en silencio el mes 13 y el dia 32, PostgreSQL 16+ lanza un error con ellos, MySQL en modo estricto (STR_TO_DATE) devuelve NULL y ClickHouse depende de si elegiste parseDateTime o la variante con sufijo OrNull. Por eso, antes de migrar, pasa por todos los motores la misma tabla pequena de casos limite: NULL, cadena vacia, mes 13, dia 32, ano de dos digitos y separadores que no coinciden. En el camino feliz de fechas ISO limpias todos los motores coinciden; divergen precisamente en esas filas.
Ten en cuenta aparte que TO_DATE(columna, ...) en una clausula WHERE es una funcion sobre la columna y bloquea un indice normal sobre ella. Si filtras por la fecha analizada en tablas grandes, analiza las cadenas una sola vez al cargar en una columna de tipo date y construye el indice sobre ella, en lugar de llamar a TO_DATE en cada consulta.
En resumen: usa TO_DATE con una plantilla explicita para formatos ambiguos, no confies en ::date con cadenas que no son ISO, recuerda que los campos fuera de rango se desbordan en silencio antes de PostgreSQL 16 pero lanzan un error en 16+, ten presente la trampa del ano de dos digitos y usa TO_TIMESTAMP cuando el valor lleve una hora.
Cuando una fecha llega como cadena en un formato que no es ISO, un simple cast
'15/03/2024'::dateo falla o la interpreta de forma distinta a la que esperas. La funcionTO_DATEanaliza la cadena con una plantilla explicita que escribes tu mismo, y eso hace que el resultado sea predecible. Aparece sobre todo al importar CSV, en migraciones y al analizar volcados manuales, donde el orden de dia, mes y ano se conoce de antemano y conviene fijarlo en la propia consulta.Sintaxis basica
TO_DATErecibe dos argumentos: la cadena de entrada y una plantilla de formato que describe como esta compuesta esa cadena.-- Input string, then the format template that describes it SELECT TO_DATE('2024-03-15', 'YYYY-MM-DD') AS d; -- result: 2024-03-15La plantilla se construye con codigos de campo:
YYYYes un ano de cuatro digitos,MMel mes,DDel dia,Monel nombre corto del mes yMonthel completo. Los literales como guiones y barras se escriben tal cual:SELECT TO_DATE('15/03/2024', 'DD/MM/YYYY') AS eu_style, TO_DATE('March 15, 2024', 'Month DD, YYYY') AS verbose, TO_DATE('20240315', 'YYYYMMDD') AS compact;Por que no usar solo ::date
El cast
::datedepende del ajustedatestyledel servidor y entiende sobre todo cadenas parecidas a ISO. Para formatos ambiguos resulta peligroso:-- Ambiguous: is this March 4 or April 3? SELECT '03/04/2024'::date; -- depends on server datestyle SELECT TO_DATE('03/04/2024', 'MM/DD/YYYY') AS as_us; -- explicit: March 4 SELECT TO_DATE('03/04/2024', 'DD/MM/YYYY') AS as_eu; -- explicit: April 3Con
TO_DATEla intencion queda codificada en la propia consulta y no depende de la configuracion del servidor. Un caso practico es cargar fechas de nacimiento de usuarios que llegaron en formato europeo:-- Imagine a staging table with raw text dates SELECT id, email, TO_DATE(raw_signup, 'DD.MM.YYYY') AS signup_day FROM users_staging;Ventajas de una plantilla explicita:
datestyleni de la configuracion regional del servidor;La trampa principal: los campos fuera de rango dependen de la version
El detalle clave de
TO_DATEes que su rigor depende de la version de PostgreSQL, y ahi es justo donde se rompen las migraciones. Los comentarios de abajo muestran el comportamiento antiguo anterior a PostgreSQL 16: el mes 13 y el dia 32 no provocaban un error, sino que se «desbordaban» hacia delante (el mes 13 pasaba a enero del ano siguiente y el dia 32 al primero del mes siguiente). Esa promocion silenciosa de una fecha invalida en una valida era la causa clasica de errores dificiles de encontrar.-- Before PostgreSQL 16: 13 is not a month, but TO_DATE rolled it over SELECT TO_DATE('2024-13-01', 'YYYY-MM-DD'); -- pre-16: 2025-01-01; 16+: errors -- Same story for day 32 SELECT TO_DATE('2024-01-32', 'YYYY-MM-DD'); -- pre-16: 2024-02-01; 16+: errors -- Separators in data and template can still mismatch silently SELECT TO_DATE('2024/03/15', 'YYYY-MM-DD'); -- still parses on any versionA partir de PostgreSQL 16 el comportamiento cambio: las dos primeras consultas ya no desbordan el exceso, sino que lanzan un error:
date/time field value out of rangepara el mes 13 ydate out of rangepara el dia 32. Los resultados2025-01-01y2024-02-01de los comentarios solo aparecen en PostgreSQL 15 y anteriores. La tercera consulta, en cambio, es permisiva en cualquier version: el desajuste de separadores entre datos y plantilla (/frente a-) sigue pasando sin quejas. Asi que, si necesitas rigor garantizado en cualquier version, no confies en la validacion de rangos integrada: comprueba el resultado formateandolo de vuelta conTO_CHAR:-- Reject rows where round-trip does not match the input SELECT raw_signup FROM users_staging WHERE TO_CHAR(TO_DATE(raw_signup, 'YYYY-MM-DD'), 'YYYY-MM-DD') <> raw_signup;Anos de dos digitos
El codigo
YYactiva una regla de ventana: PostgreSQL completa un ano de dos digitos en relacion con el actual, lo que casi nunca es lo que quieres para fechas historicas.-- YY uses a sliding window around the current year SELECT TO_DATE('15-03-49', 'DD-MM-YY'); -- 2049, maybe fine SELECT TO_DATE('15-03-99', 'DD-MM-YY'); -- 1999, sliding-window guess -- Force the century explicitly with RR or just demand 4 digits SELECT TO_DATE('15-03-1999', 'DD-MM-YYYY'); -- no guessingConsejo: exige
YYYYsiempre que puedas y normaliza los datos de origen a anos de cuatro digitos para eliminar por completo la adivinanza.TO_DATE frente a TO_TIMESTAMP
TO_DATEdescarta la parte de la hora: el resultado es siempre de tipodate. Si la cadena tiene horas, minutos y segundos, usaTO_TIMESTAMP, de lo contrario perderas precision en silencio.-- TO_DATE drops the time portion entirely SELECT TO_DATE('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI'); -- 2024-03-15 -- TO_TIMESTAMP keeps hours, minutes, seconds SELECT TO_TIMESTAMP('2024-03-15 14:30', 'YYYY-MM-DD HH24:MI'); -- 2024-03-15 14:30:00Diferencias entre motores:
TO_DATE; usaSTR_TO_DATE('15/03/2024', '%d/%m/%Y')con codigos de porcentaje, y en modo estricto devuelveNULLante una fecha invalida.parseDateTimeBestEfforty unaparseDateTimeestricta con plantilla al estilo Java; usa el sufijoOrNullpor seguridad.-- MySQL equivalent uses percent-style format codes SELECT STR_TO_DATE('15/03/2024', '%d/%m/%Y') AS d;Al trasladar el analisis de fechas entre PostgreSQL, MySQL y ClickHouse, el verdadero peligro no es la sintaxis de
TO_DATE, sino la diferencia en como trata cada motor los valores invalidos. PostgreSQL anterior al 16 desborda en silencio el mes 13 y el dia 32, PostgreSQL 16+ lanza un error con ellos, MySQL en modo estricto (STR_TO_DATE) devuelveNULLy ClickHouse depende de si elegisteparseDateTimeo la variante con sufijoOrNull. Por eso, antes de migrar, pasa por todos los motores la misma tabla pequena de casos limite: NULL, cadena vacia, mes 13, dia 32, ano de dos digitos y separadores que no coinciden. En el camino feliz de fechas ISO limpias todos los motores coinciden; divergen precisamente en esas filas.Ten en cuenta aparte que
TO_DATE(columna, ...)en una clausulaWHEREes una funcion sobre la columna y bloquea un indice normal sobre ella. Si filtras por la fecha analizada en tablas grandes, analiza las cadenas una sola vez al cargar en una columna de tipodatey construye el indice sobre ella, en lugar de llamar aTO_DATEen cada consulta.En resumen: usa
TO_DATEcon una plantilla explicita para formatos ambiguos, no confies en::datecon cadenas que no son ISO, recuerda que los campos fuera de rango se desbordan en silencio antes de PostgreSQL 16 pero lanzan un error en 16+, ten presente la trampa del ano de dos digitos y usaTO_TIMESTAMPcuando el valor lleve una hora.