MENU
  1. Inicio
  2. SQL
  3. Cómo convertir cadenas de fecha en formato específico a tipo DATE en SQL

Cómo convertir cadenas de fecha en formato específico a tipo DATE en SQL

SQL

Al trabajar con bases de datos en SQL, a menudo es necesario convertir cadenas de fecha en formatos específicos al tipo DATE. Por ejemplo, cuando los datos de entrada del usuario o de sistemas externos no siguen el formato estándar de fechas, es necesario realizar la conversión adecuada para asegurar que las consultas funcionen correctamente. En este artículo, explicaremos detalladamente cómo convertir cadenas de fecha en formatos específicos a tipo DATE en SQL para las principales bases de datos (MySQL, PostgreSQL, SQL Server, Oracle), lo que permitirá manejar datos de fecha de manera coherente y realizar operaciones de bases de datos de manera más eficiente.

Índice

Comprender los formatos de fecha en SQL

Al trabajar con fechas en SQL, es importante entender las diferencias en los formatos de fecha. Los formatos de fecha varían según la base de datos, aunque generalmente se utiliza el formato YYYY-MM-DD (año-mes-día). Sin embargo, los datos de entrada del usuario o de fuentes externas pueden utilizar diferentes formatos. Por lo tanto, es necesario conocer el formato de fecha exacto y aplicar el método de conversión adecuado.

Formato de fecha estándar

En muchos sistemas de bases de datos, el formato estándar para las fechas es YYYY-MM-DD. Este formato es un estándar internacional y se recomienda para manejar fechas de manera consistente.

Ejemplos de diferentes formatos de fecha

Los datos ingresados por el usuario o importados de otros sistemas pueden utilizar formatos de fecha diferentes, como los siguientes:

  • MM/DD/YYYY
  • DD-MM-YYYY
  • YYYY/MM/DD
  • DD Mon YYYY

Razones para convertir formatos de fecha

Si no se unifican los formatos de fecha, las comparaciones o cálculos de fechas no se realizarán con precisión. Además, los resultados de las consultas pueden no ser los esperados. Por eso es importante realizar las conversiones adecuadas de los formatos de fecha.

A continuación, explicaremos la sintaxis básica para convertir cadenas de fecha en tipo DATE en SQL.

Sintaxis básica para la conversión de formatos de fecha

Para convertir una cadena de fecha al tipo DATE en SQL, se utilizan funciones o sintaxis específicas proporcionadas por cada sistema de bases de datos. A continuación, mostramos la sintaxis básica para los sistemas de bases de datos más comunes.

Función DATE_FORMAT en MySQL

En MySQL, se utiliza la función STR_TO_DATE para convertir una cadena de fecha al tipo DATE. Esta función convierte la cadena según el formato especificado.

SELECT STR_TO_DATE('31-12-2023', '%d-%m-%Y');

En este ejemplo, la cadena de fecha 31-12-2023 se convierte al tipo DATE.

Función TO_DATE en PostgreSQL

En PostgreSQL, se utiliza la función TO_DATE para convertir una cadena de fecha al tipo DATE.

SELECT TO_DATE('31-12-2023', 'DD-MM-YYYY');

En este ejemplo, la cadena 31-12-2023 se convierte al tipo DATE.

Función CONVERT en SQL Server

En SQL Server, se utiliza la función CONVERT para convertir una cadena de fecha al tipo DATE. La función CONVERT determina el formato mediante un código de estilo.

SELECT CONVERT(DATE, '12/31/2023', 101);

En este ejemplo, la cadena 12/31/2023 se convierte al tipo DATE.

Función TO_DATE en Oracle

En Oracle, se utiliza la función TO_DATE para convertir una cadena de fecha al tipo DATE.

SELECT TO_DATE('31-Dec-2023', 'DD-Mon-YYYY') FROM dual;

En este ejemplo, la cadena 31-Dec-2023 se convierte al tipo DATE.

Comprender los métodos básicos de conversión de formatos de fecha en cada sistema de bases de datos permite manejar datos de fecha con precisión. A continuación, explicaremos cómo realizar conversiones específicas en cada base de datos.

Método de conversión de cadenas de fecha en MySQL

En MySQL, se utiliza la función STR_TO_DATE para convertir una cadena de fecha al tipo DATE. Esta función analiza la cadena de acuerdo con el formato especificado y la convierte a un valor de tipo DATE.

Uso básico de la función STR_TO_DATE

La función STR_TO_DATE se utiliza de la siguiente manera:

SELECT STR_TO_DATE('31-12-2023', '%d-%m-%Y');

En este ejemplo, la cadena de fecha 31-12-2023 se convierte al tipo DATE. Los especificadores de formato corresponden a cada parte de la cadena de fecha:

  • %d: Día
  • %m: Mes
  • %Y: Año

Cómo manejar múltiples formatos

Cuando se trabajan con cadenas de fecha en diferentes formatos, se utilizan los especificadores de formato correspondientes a cada uno.

SELECT STR_TO_DATE('2023/12/31', '%Y/%m/%d');
SELECT STR_TO_DATE('12-31-2023', '%m-%d-%Y');

Estos ejemplos muestran cómo convertir cadenas en diferentes formatos al tipo DATE.

Ejemplo práctico

En operaciones de bases de datos reales, estas funciones pueden utilizarse para convertir las cadenas de fecha almacenadas en tablas.

SELECT STR_TO_DATE(order_date, '%d-%m-%Y') AS formatted_date
FROM orders;

En este ejemplo, las cadenas de fecha almacenadas en la columna order_date de la tabla orders se convierten al tipo DATE y se muestran con el alias formatted_date.

La conversión de formatos de fecha en MySQL es sencilla con la función STR_TO_DATE. A continuación, explicamos cómo convertir cadenas de fecha en PostgreSQL.

Método de conversión de cadenas de fecha en PostgreSQL

En PostgreSQL, se utiliza la función TO_DATE para convertir una cadena de fecha al tipo DATE. Esta función analiza la cadena de acuerdo con el formato especificado y la convierte a un valor de tipo DATE.

Uso básico de la función TO_DATE

La función TO_DATE se utiliza de la siguiente manera:

SELECT TO_DATE('31-12-2023', 'DD-MM-YYYY');

En este ejemplo, la cadena de fecha 31-12-2023 se convierte al tipo DATE. Los especificadores de formato corresponden a cada parte de la cadena de fecha:

  • DD: Día
  • MM: Mes
  • YYYY: Año

Cómo manejar múltiples formatos

Cuando se trabajan con cadenas de fecha en diferentes formatos, se utilizan los especificadores de formato correspondientes a cada uno.

SELECT TO_DATE('2023/12/31', 'YYYY/MM/DD');
SELECT TO_DATE('12-31-2023', 'MM-DD-YYYY');

Estos ejemplos muestran cómo convertir cadenas en diferentes formatos al tipo DATE.

Ejemplo práctico

En operaciones de bases de datos reales, estas funciones pueden utilizarse para convertir las cadenas de fecha almacenadas en tablas.

SELECT TO_DATE(order_date, 'DD-MM-YYYY') AS formatted_date
FROM orders;

En este ejemplo, las cadenas de fecha almacenadas en la columna order_date de la tabla orders se convierten al tipo DATE y se muestran con el alias formatted_date.

Cómo convertir formatos complejos

Las cadenas de fecha con formatos complejos, que incluyen caracteres o símbolos específicos, también pueden convertirse utilizando los especificadores de formato correspondientes.

SELECT TO_DATE('31-Dec-2023', 'DD-Mon-YYYY');

En este ejemplo, la cadena 31-Dec-2023 se convierte al tipo DATE.

La conversión de formatos de fecha en PostgreSQL es sencilla con la función TO_DATE. A continuación, explicamos cómo convertir cadenas de fecha en SQL Server.

Método de conversión de cadenas de fecha en SQL Server

En SQL Server, se utilizan las funciones CONVERT y TRY_CONVERT para convertir una cadena de fecha al tipo DATE. Estas funciones analizan la cadena según el código de estilo especificado y la convierten a un valor de tipo DATE.

Uso básico de la función CONVERT

La función CONVERT se utiliza de la siguiente manera:

SELECT CONVERT(DATE, '12/31/2023', 101);

En este ejemplo, la cadena 12/31/2023 se convierte al tipo DATE. El código de estilo 101 indica que la cadena de fecha sigue el formato MM/DD/YYYY.

Uso de la función TRY_CONVERT

La función TRY_CONVERT devuelve NULL si la conversión falla, lo que permite manejar la conversión de manera segura.

SELECT TRY_CONVERT(DATE, '31-12-2023', 105);

En este ejemplo, la cadena de fecha 31-12-2023 se convierte al tipo DATE. El código de estilo 105 indica que la cadena de fecha sigue el formato DD-MM-YYYY.

Cómo manejar múltiples formatos

Cuando se trabajan con cadenas de fecha en diferentes formatos, se utilizan los códigos de estilo correspondientes a cada uno.

SELECT CONVERT(DATE, '2023/12/31', 111);  -- YYYY/MM/DD
SELECT CONVERT(DATE, '12-31-2023', 110);  -- MM-DD-YYYY

Estos ejemplos muestran cómo convertir cadenas en diferentes formatos al tipo DATE.

Ejemplo práctico

En operaciones de bases de datos reales, estas funciones pueden utilizarse para convertir las cadenas de fecha almacenadas en tablas.

SELECT CONVERT(DATE, order_date, 101) AS formatted_date
FROM orders;

En este ejemplo, las cadenas de fecha almacenadas en la columna order_date de la tabla orders se convierten al tipo DATE y se muestran con el alias formatted_date.

Lista de especificadores de formato de fecha

A continuación se muestra una lista de algunos de los códigos de estilo comunes utilizados en SQL Server:

  • 101: MM/DD/YYYY
  • 102: YYYY.MM.DD
  • 103: DD/MM/YYYY
  • 104: DD.MM.YYYY
  • 105: DD-MM-YYYY

La conversión de formatos de fecha en SQL Server es sencilla con las funciones CONVERT y TRY_CONVERT. A continuación, explicamos cómo convertir cadenas de fecha en Oracle.

Método de conversión de cadenas de fecha en Oracle

En Oracle, se utiliza la función TO_DATE para convertir una cadena de fecha al tipo DATE. Esta función analiza la cadena de acuerdo con el formato especificado y la convierte a un valor de tipo DATE.

Uso básico de la función TO_DATE

La función TO_DATE se utiliza de la siguiente manera:

SELECT TO_DATE('31-12-2023', 'DD-MM-YYYY') FROM dual;

En este ejemplo, la cadena de fecha 31-12-2023 se convierte al tipo DATE. Los especificadores de formato corresponden a cada parte de la cadena de fecha:

  • DD: Día
  • MM: Mes
  • YYYY: Año

Cómo manejar múltiples formatos

Cuando se trabajan con cadenas de fecha en diferentes formatos, se utilizan los especificadores de formato correspondientes a cada uno.

SELECT TO_DATE('2023/12/31', 'YYYY/MM/DD') FROM dual;
SELECT TO_DATE('12-31-2023', 'MM-DD-YYYY') FROM dual;

Estos ejemplos muestran cómo convertir cadenas en diferentes formatos al tipo DATE.

Ejemplo práctico

En operaciones de bases de datos reales, estas funciones pueden utilizarse para convertir las cadenas de fecha almacenadas en tablas.

SELECT TO_DATE(order_date, 'DD-MM-YYYY') AS formatted_date
FROM orders;

En este ejemplo, las cadenas de fecha almacenadas en la columna order_date de la tabla orders se convierten al tipo DATE y se muestran con el alias formatted_date.

Cómo convertir formatos complejos

Las cadenas de fecha con formatos complejos, que incluyen caracteres o símbolos específicos, también pueden convertirse utilizando los especificadores de formato correspondientes.

SELECT TO_DATE('31-Dec-2023', 'DD-Mon-YYYY') FROM dual;

En este ejemplo, la cadena 31-Dec-2023 se convierte al tipo DATE. Mon indica la abreviatura del mes.

Lista de especificadores de formato

A continuación se muestra una lista de algunos de los especificadores de formato comunes utilizados en Oracle:

  • YYYY: Año de 4 dígitos
  • MM: Mes de 2 dígitos
  • DD: Día de 2 dígitos
  • Mon: Abreviatura del mes (ej: Jan, Feb, Mar)
  • HH24: Hora en formato 24 horas
  • MI: Minutos
  • SS: Segundos

La conversión de formatos de fecha en Oracle es sencilla con la función TO_DATE. A continuación, explicamos los errores comunes al realizar conversiones de fecha y cómo depurarlos.

Manejo de errores y depuración

Al convertir cadenas de fecha al tipo DATE, pueden ocurrir varios errores, como la incompatibilidad de formato o fechas no válidas. A continuación, explicamos cómo detectar y manejar estos errores de manera efectiva.

Tipos comunes de errores

Al convertir fechas, los errores más comunes incluyen:

  • Incompatibilidad de formato: ocurre cuando el formato especificado no coincide con el formato de la cadena de fecha.
  • Fecha no válida: ocurre cuando se intenta convertir una fecha inexistente (ej: 30-02-2023).
  • Valores NULL: la conversión puede fallar si la cadena de fecha es NULL.

Detección y manejo de errores

A continuación, mostramos cómo detectar y manejar errores de conversión de fechas en diferentes sistemas de bases de datos.

MySQL

En MySQL, si la cadena de fecha no es válida, la función devuelve NULL. Se puede utilizar la función IFNULL o COALESCE para asignar un valor predeterminado.

SELECT IFNULL(STR_TO_DATE(order_date, '%d-%m-%Y'), '1970-01-01') AS formatted_date
FROM orders;

En este ejemplo, si la conversión falla, se devuelve el valor predeterminado 1970-01-01.

PostgreSQL

En PostgreSQL, cuando una cadena de fecha no válida se convierte mediante TO_DATE, se genera una excepción. Esto puede manejarse utilizando la estructura TRY...EXCEPT.

DO $$
BEGIN
  SELECT TO_DATE('invalid-date', 'DD-MM-YYYY');
EXCEPTION
  WHEN others THEN
    RAISE NOTICE 'Invalid date format';
END $$;

En este ejemplo, si la conversión falla, se muestra un mensaje de error.

SQL Server

En SQL Server, la función TRY_CONVERT devuelve NULL si la conversión falla.

SELECT TRY_CONVERT(DATE, 'invalid-date', 105) AS formatted_date;

En este ejemplo, una cadena de fecha no válida se devuelve como NULL.

Oracle

En Oracle, se puede utilizar el bloque EXCEPTION para manejar errores.

BEGIN
  SELECT TO_DATE('invalid-date', 'DD-MM-YYYY') FROM dual;
EXCEPTION
  WHEN others THEN
    DBMS_OUTPUT.PUT_LINE('Invalid date format');
END;

En este ejemplo, si la conversión falla, se muestra un mensaje de error.

Puntos clave para la depuración

A continuación, se muestran algunos puntos clave para depurar errores de conversión de fechas:

  • Verificar que los especificadores de formato sean correctos.
  • Validar que las cadenas de fecha de entrada sean válidas.
  • Verificar que las funciones de conversión admitan el formato de fecha.

Al utilizar formatos correctos y cadenas de fecha válidas, se puede minimizar la aparición de errores en la conversión de fechas. A continuación, mostramos ejemplos avanzados y ejercicios para poner en práctica lo aprendido.

Ejemplos avanzados y ejercicios sobre la conversión de cadenas de fecha a tipo DATE

Ejemplo avanzado: Conversión de fechas en múltiples formatos

Supongamos que tenemos una tabla con varias cadenas de fecha en diferentes formatos. A continuación mostramos cómo unificarlas y convertirlas al tipo DATE.

Ejemplo de datos

Supongamos que tenemos la siguiente tabla events.

CREATE TABLE events (
    event_id INT,
    event_date VARCHAR(20)
);

INSERT INTO events (event_id, event_date) VALUES
(1, '2023-12-31'),
(2, '31/12/2023'),
(3, 'December 31, 2023');

Ejemplo de consulta de conversión

A continuación mostramos una consulta que convierte cada formato.

SELECT event_id,
    CASE
        WHEN event_date LIKE '____-__-__' THEN STR_TO_DATE(event_date, '%Y-%m-%d')
        WHEN event_date LIKE '__/__/____' THEN STR_TO_DATE(event_date, '%d/%m/%Y')
        WHEN event_date LIKE '%____%' THEN STR_TO_DATE(event_date, '%M %d, %Y')
        ELSE NULL
    END AS formatted_date
FROM events;

Esta consulta utiliza la sentencia CASE para manejar cada formato y convertir las cadenas de fecha al tipo DATE.

Ejercicios

A continuación, presentamos algunos ejercicios para practicar la conversión de cadenas de fecha a tipo DATE.

Ejercicio 1

Escribe una consulta para convertir la siguiente cadena de fecha al tipo DATE:

  • Cadena de fecha: 15-08-2023
  • Formato: DD-MM-YYYY

Ejercicio 2

Escribe una consulta para convertir las cadenas de fecha almacenadas en la columna sale_date de la tabla sales al tipo DATE, y mostrar el resultado en una nueva columna formatted_sale_date.

CREATE TABLE sales (
    sale_id INT,
    sale_date VARCHAR(20)
);

INSERT INTO sales (sale_id, sale_date) VALUES
(1, '08/15/2023'),
(2, '15-08-2023'),
(3, '2023.08.15');
  • Los formatos de cada cadena de fecha son los siguientes:
  • 08/15/2023 : MM/DD/YYYY
  • 15-08-2023 : DD-MM-YYYY
  • 2023.08.15 : YYYY.MM.DD

Ejercicio 3

Escribe una consulta para convertir las cadenas de fecha almacenadas en la columna birthday de la tabla birthdays al tipo DATE, y devolver NULL en caso de que la cadena sea una fecha inválida.

CREATE TABLE birthdays (
    user_id INT,
    birthday VARCHAR(20)
);

INSERT INTO birthdays (user_id, birthday) VALUES
(1, '1990-02-30'),
(2, '29-02-2020'),
(3, '15/08/1995');
  • Los formatos de cada cadena de fecha son los siguientes:
  • 1990-02-30 : YYYY-MM-DD
  • 29-02-2020 : DD-MM-YYYY
  • 15/08/1995 : DD/MM/YYYY

Estos ejercicios te ayudarán a profundizar en la técnica de conversión de cadenas de fecha. A continuación, se presenta un resumen del artículo.

Resumen

En este artículo, hemos explicado cómo convertir cadenas de fecha en formatos específicos al tipo DATE en SQL. Aprendimos los métodos específicos de conversión para los principales sistemas de bases de datos (MySQL, PostgreSQL, SQL Server, Oracle) y cómo manejar los errores y depurar los problemas comunes. Finalmente, a través de ejemplos avanzados y ejercicios prácticos, profundizamos en las técnicas de conversión de cadenas de fecha.

La conversión de formatos de fecha es crucial en la manipulación de bases de datos. Realizar conversiones precisas asegura la integridad de los datos y mejora la fiabilidad de las consultas. Asegúrate de tener siempre en cuenta los siguientes puntos:

  • Utiliza los especificadores de formato correctos.
  • Valida que el formato de las cadenas de fecha sea correcto.
  • Maneja adecuadamente las fechas inválidas o incompatibilidades de formato.
  • Comprende y aplica las funciones y sintaxis específicas de cada sistema de bases de datos.

Utiliza estos conocimientos y técnicas para manejar datos de fecha de manera precisa y aprovechar al máximo las capacidades de SQL para optimizar la gestión y operación de bases de datos.

SQL
Manejo de Errores SQL PostgreSQL MySQL Oracle SQL Server Base de Datos Cadena de Fecha Conversión a DATE Conversión de Formato
Índice