Funciones PLSQL TO_CHAR, EXTRACT, TO_DATE y TO_TIMESTAMP (conversión de fechas a caracteres y viceversa)

Este artículo es continuación del anterior en el que hablé sobre los tipos de dato fecha DATE, TIMESTAMP e INTERVAL y en él hablaré sobre las funciones PL/SQL que se pueden utilizar para convertir datos de tipo fecha en datos de tipo carácter y viceversa.

Pero antes de hablar de estas funciones, resulta conveniente conocer como se puede obtener desde PLSQL el valor de la fecha y el tiempo actual. Seguro que la gran mayoría de vosotros, si sois programadores PL/SQL, ya conocéis la clásica función SYSDATE, función que, sin duda, es la más empleada en este sentido. No obstante, la base de datos Oracle ofrece la posibilidad de utilizar otras funciones que proporcionan diferentes variantes del valor de la fecha y el tiempo actual y que veremos a continuación.

Las funciones PLSQL de las que hablo son:

FUNCION SQL        ZONA HORARIA      TIPO DE DATO DEVUELTO
CURRENT_DATE       Sesión            DATE
CURRENT_TIMESTAMP  Sesión            TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP     Sesión            TIMESTAMP
SYSDATE            Servidor de BBDD  DATE
SYSTIMESTAMP       Servidor de BBDD  TIMESTAMP WITH TIME ZONE

En el siguiente ejemplo podéis ver los valores devueltos por las funciones SYSDATE y SYSTIMESTAMP.

BEGIN
DBMS_OUTPUT.put_line (SYSDATE);
DBMS_OUTPUT.put_line (SYSTIMESTAMP);
DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);
END;
/

21-FEB-12
21-FEB-12 19.21.17.437000000 AM -05:00
-000000000 00:00:00.437000000

En el ejemplo podemos observar que al haber utilizado la función PLSQL DBMS_OUTPUT.PUT_LINE para mostrar los valores entregados por las funciones SYSDATE y SYSTIMESTAMP, la base de datos Oracle implícitamente convierte los datos devueltos en una cadena de caracteres, para ello utiliza el formato fecha que por defecto tiene asignado la base de datos o la sesión (esto se especifica al definir el valor del parámetro NLS de base de datos denominado NLS_DATE_FORMAT, otros parámetros relacionados son NLS_TIMESTAMP_FORMAT y NLS_TIME_FORMAT). La instalación por defecto de cualquier base de datos Oracle configura el formato de fecha con el valor DD-MON-YYYY.

También debéis daros cuenta de que al restar al valor devuelto por la función SYSTIMESTAMP el valor entregado por SYSDATE, el resultado es un intervalo que, aun siendo muy cercano a cero, no es exactamente cero.

Conversión de fechas a carácter

De igual manera que con la función TO_CHAR es posible convertir números a caracteres, otra versión de la misma función nos permite hacer lo mismo con los tipos de dato asociados con fechas y tiempo. De igual manera que con los números, TO_CHAR ofrecen un gran número de posibilidades para formatear fechas y que estas aparezcan tal y como queramos.

Veamos algunos ejemplos:

Si utilizamos TO_CHAR sin ningún tipo de mascara de formato, entonces la cadena de caracteres devuelta por la función será la misma que devuelve la base de datos Oracle cuando realiza una conversión implícita (según vimos que ocurría al emplear la función DBMS_OUTPUT.PUT_LINE).

BEGIN
  DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE));
  DBMS_OUTPUT.put_line (TO_CHAR (SYSTIMESTAMP));
END;
/

21-FEB-12
21-FEB-12 19.21.17.437000000 AM -05:00

Si queremos que la función PL/SQL TO_CHAR devuelva el día de la semana y el nombre del mes, entonces utilizaríamos la siguiente máscara:

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
             'Day, DD "de" Month "de" YYYY'));
END;
/

Martes   , 21 de Febrero    de 2012

En este sentido debemos saber que el idioma utilizado para mostrar los datos de la fecha viene determinado por el valor del parámetro de la base de datos Oracle NLS_DATE_LANGUAGE, un valor que también puede pasarse como tercer argumento de la función TO_CHAR como podemos ver en el siguiente ejemplo:

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
             'Day, DDth Month YYYY',
             'NLS_DATE_LANGUAGE=English'));
END;
/

Tuesday  , 21ST February  2012

En los anteriores ejemplos observaréis que la función TO_CHAR devuelve algunos caracteres extra en blanco, esto ocurre porque por defecto la base de datos Oracle añade dichos caracteres hasta completar la máxima longitud posible del día de la semana o del mes. Esta claro que en la mayoría de los casos querremos evitar que estos caracteres extra aparezcan, para ello disponemos del elemento formateador FM, que si lo añadimos al principio de nuestra máscara eliminará los mencionados espacios.

BEGIN
  DBMS_OUTPUT.put_line (
    TO_CHAR (SYSDATE, 
             'FMDay, DD "de" Month "de" YYYY'));
END;
/

Martes, 21 de Febrero de 2012

También es posible utilizar determinadas formatos que nos permitirán mostrar informaciones variadas relacionadas con una fecha.

Cuatrimestre:
TO_CHAR (SYSDATE, 'Q') -> 1

Día del año:
TO_CHAR (SYSDATE, 'DDD') -> 052

Diferentes formatos de fecha y hora:
TO_CHAR (SYSDATE, 'DD-MM-YYYY HH24:MI:SS') -> 21-02-2012 14:06:30
TO_CHAR (SYSDATE, 'DD-MON-YY HH:MI:SS AM') -> 21-FEB-12 02:06:30 PM

Otra de las funciones PL/SQL que permite manejar fechas es la función EXTRACT, una función que permite extraer los valores de diferentes elementos de un dato de tipo fecha. A continuación os dejo diferentes empleos de la misma.

Año:
EXTRACT (YEAR FROM SYSDATE) -> 2012

Día del mes:
EXTRACT (DAY FROM SYSDATE) -> 21

Por otro lado, para convertir una cadena de caracteres a un dato de tipo fecha se utilizan las funciones TO_DATE y TO_TIMESTAMP. Estas funciones también admiten la utilización de una máscara para indicar el formato de la fecha y para configurar el idioma de la misma.

A continuación os dejo un ejemplo con varios usos de la función TO_DATE.

DECLARE
  l_date DATE;
BEGIN
  l_date := TO_DATE ('21-FEB-2012');
  l_date := TO_DATE ('022112', 'MMDDYY');
  l_date := TO_DATE ('21-Feb-12', 'DD-Mon-YY HH:MI:SS');
  l_date := TO_DATE ('Feb/21/12', 'Mon/DD/YY HH:MI:SS');
  l_date := TO_DATE ('Febrero.21.2012', 'Month.DD.YYYY HH:MI:SS');
END;

Obviamente si la máscara que pasamos a las funciones TO_DATE o TO_TIMESTAMP no se corresponde con el formato de la fecha que queremos convertir, la base de datos Oracle generará un error.

Por ejemplo, la siguiente consulta SQL devolverá el error "ORA-01843: mes no válido":

SELECT TO_DATE ('21-02-2012', 'DD-MON-YYYY')
FROM dual;

Desde el punto de vista de la programación PL/SQL, conviene tener mucha precaución a la hora de utilizar las funciones TO_DATE o TO_TIMESTAMP, sobre todo si se utilizan sin especificar una máscara concreta (es decir, utilizarán la máscara que emplea la base de datos Oracle por defecto), ya que cambios posteriores del parámetro de base de datos NLS_DATE_FORMAT pueden provocar errores en nuestro programa.