Siguiendo con el tema de algunos de los artículos de este blog, hoy pretendemos dejar ya zanjado el tutorial sobre como podemos trabajar en PL/SQL con los campos tipo DATE y TIMESTAMP de las bases de datos Oracle. En esta ocasión empezaremos hablando de las operaciones aritméticas que podemos realizar con estos campos y terminaremos escribiendo sobre la función estándar del PLSQL TRUNC.
Las bases de datos Oracle permiten realizar una gran variedad de operaciones aritméticas con los tipos de datos DATE y TIMESTAMP, pudiendo realizar dichas operaciones de diversas maneras.
Así, por ejemplo, con una fecha podremos realizar las siguientes operaciones aritméticas:
- Sumarle o substraerle un valor numérico (por ejemplo, SYSDATE + 5). En este caso la base de datos Oracle trata los números como número de días.
- Substraerle otra fecha (por ejemplo, SYSDATE - TO_DATE('30-03-2012', 'DD-MM-YYYY')). El resultado será un número que indicará el número de días de diferencia entre ambas fechas. Por otro lado, si intentamos sumar dos fechas la base de datos Oracle devolverá el error “ORA-00975: fecha + fecha no permitido”.
- Utilizar diferentes funciones estándar del PL/SQL, que mencionaremos más adelante, para “desplazar” una fecha un número de meses determinado o a otra fecha dentro de una misma semana.
A continuación os dejo algunos ejemplos de operaciones aritméticas con una fecha y un número (en todos los casos deberéis asumir que la variable l_fecha ha sido declarada como de tipo DATE):
1. Asignación a una variable del día de mañana: l_fecha := SYSDATE + 1; 2. Sumar a la hora actual dos horas: l_fecha := SYSDATE + 2/24; 3. Restar a la hora actual 12 minutos: l_fecha := SYSDATE + 12/(60*24); 4. Restar a la hora actual 25 segundos: l_fecha := SYSDATE + 25/(60*60*24);
Según he indicado anteriormente, cuando restamos dos fechas el resultado devuelve el número de días que hay entre ambas. Debajo os dejo un sencillo ejemplo en el que encontraréis un función PLSQL que permite calcular la edad en años de una persona, asumiendo que un año tiene 365 días y que como parámetro de entrada se le pasa el día de nacimiento de dicha persona. Obviamente existirá un pequeño error ya que no estaremos teniendo en cuenta los años bisiestos.
CREATE OR REPLACE FUNCTION su_edad (dia_nacimiento IN DATE) RETURN NUMBER IS BEGIN RETURN (SYSDATE – dia_nacimiento)/365; END su_edad;
Por otro lado, según he mencionado también anteriormente, la base de datos Oracle ofrece varias funciones estándar para “desplazar” fechas. Estas funciones son:
- ADD_MONTHS: añade o resta el número de meses indicado a una fecha determinada.
- NEXT_DAY: devuelve la fecha del siguiente día de la semana que se corresponde con el valor del parámetro pasado en la función.
- LAST_DAY: devuelve la fecha del último día del mes de la fecha especificada.
Veamos a continuación algunos ejemplos:
1. Sumar dos meses a la fecha actual: l_fecha := ADD_MONTHS(SYSDATE, 2); 2. Restar seis meses a la fecha actual: l_fecha := ADD_MONTHS(SYSDATE, -6); 3. Restar un mes al 27 de febrero: l_fecha := ADD_MONTHS (TO_DATE('27-FEB-2012', 'DD-MON-YYYY'), -1); Resultado: 27-ENE-2012 4. Sumar un mes al 30 de abril: l_fecha := ADD_MONTHS (TO_DATE('30-ABR-2012', 'DD-MON-YYYY'), 1); Resultado: 31-MAY-2012 5. Restar un mes al 29 de febrero (en un año bisiesto): l_fecha := ADD_MONTHS (TO_DATE('29-FEB-2012', 'DD-MON-YYYY'), -1); Resultado: 31-ENE-2012 6. Sumar un mes al 31 de marzo: l_fecha := ADD_MONTHS (TO_DATE('31-MAR-2012', 'DD-MON-YYYY'), 1); Resultado: 30-ABR-2012 7. Encontrar el siguiente miércoles a la fecha actual: l_fecha := NEXT_DAY(SYSDATE, 'Mié'); o l_fecha := NEXT_DAY(SYSDATE, 'Miércoles'); 8. Encontrar el ultimo día del mes correspondiente a la fecha actual: l_fecha := LAST_DAY(SYSDATE);
Viendo el anterior ejemplo, podemos observar que cuando nos encontramos en una fecha de fin de mes, al sumar o restar a dicha fecha un mes, el resultado es también el último día del mes correspondiente, independientemente de que el mes al que se le suma sea de 28 (29 si es bisiesto), 30 ó 31 días. El motivo es que la base de datos Oracle detecta que como parámetro le estamos pasando el último día de un mes y, por lo tanto, devolverá el último día del mes correspondiente a la suma o resta del segundo parámetro pasado.
Por otro lado, al utilizar la función NEXT_DAY, debemos indicar el día de la semana en el idioma que tenga configurada nuestra sesión Oracle (incluyendo acentos), idioma que vendrá determinado por el valor del parámetro NLS_DATE_LANGUAGE.
Truncamiento de fechas (función TRUNC)
La función estándar PL/SQL TRUNC se emplea para truncar una fecha a una unidad de medida determinada. El uso más común de la función TRUNC es directamente TRUNC(l_fecha), sin especificar ningún tipo de máscara, de manera que lo que hace en este caso la función es poner el tiempo de l_fecha a 00:00:00.
A continuación os dejo algunos ejemplos del uso de la función TRUNC:
1. Obtener la fecha de hoy pero con el tiempo a 00:00:00: l_fecha := TRUNC(SYSDATE); 2. Obtener el primer día del mes del día actual: l_fecha := TRUNC(SYSDATE, 'MM'); 3. Obtener el primer día del cuarto de mes del día actual: l_fecha := TRUNC(SYSDATE, 'Q'); 4. Obtener el primer día del año del día actual: l_fecha := TRUNC(SYSDATE, 'Y');
Con este artículo pongo fin a la serie de artículos relativos al trabajo con campos de tipo DATE y TIMESTAMP, espero que os sean de utilidad.
Artículos relacionados:
Funciones PLSQL TO_CHAR, EXTRACT, TO_DATE y TO_TIMESTAMP
Tipos de datos Oracle DATE, TIMESTAMP e INTERVAL
6 comentarios:
Muy interesante tu publicación, pero me queda una duda.
Yo obtengo el año actual (numérico) de la siguiente forma TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))
¿Existe una forma más optima?
Saludos
Hola Mauricio, la forma que indicas es totalmente óptima.
Hola. Como puedo compara la fecha de hoy solo día y mes con la fecha de nacimiento?? es para crear una notificación de cumpleaños.
Diana es muy sencillo. Por ejemplo la comparación:
IF TO_CHAR(SYSDATE, 'DDMM') = '1712' THEN
Serviría para crear una notificación todos los 17 de diciembre.
Hola, estoy intentando consultar datos desde el mes actual hasta 2 meses despues, pero me está dando error, sabes si está de la manera correcta?
between TO_CHAR(trunc(SYSDATE,'YYYYMM')) AND ADD_MONTHS(TO_CHAR(TRUNC(SYSDATE,'YYYYMM')),2)
como agrupar por fecha pero cada 5 días en 1 año
Publicar un comentario