Objetivo
•Utilizar funciones de fecha en Sentencias SQL.
NOTA: Usamos como ejemplo la base de datos: ORCL, la cual viene por defecto en cualquier versión de ORACLE.
Manejo de las Fechas
Formato de Fecha de Oracle: La base de datos Oracle almacena las fechas en un formato numérico interno, que representa el siglo, el año, el mes, el día, las horas, los minutos y los segundos.
El formato de visualización y de entrada de ORACLE(por defecto) para cualquier fecha es DD-MON-RR. Las fechas válidas van del 1 de enero de 4712 a.C. al 31 de diciembre de 9999 d.C.
Las fechas en el campo HIRE_DATE de la tabla Employees se muestran en formato DD-MON-RR(por defecto). Sin embargo, las fechas no se almacenan en la base de datos en este formato. Se almacenan todos los componentes de fecha y hora por separados. Así pues, aunque una fecha como 17-JUN-87 se muestra como día, mes y año, también hay información de hora y de siglo asociada a la fecha. Los datos completos podrían ser 17 de junio de 1987, 5:10:43 PM.Esta fecha se almacena internamente así:
| SIGLO | AÑO | MES | DÍA | HORA | MINUTO | SEGUNDO |
| 19 | 87 | 6 | 17 | 17 | 10 | 43 |
Siglos y el Año 2000 : Al insertar un registro con una columna de fecha en una tabla, la información de siglo se selecciona en la función SYSDATE. Sin embargo, cuando la columna de fecha se muestra en la pantalla, el componente de siglo no se muestra (por defecto).
El tipo de datos DATE almacena siempre la información de año como número de cuatro dígitos internamente: dos dígitos para el siglo y dos para el año. Por ejemplo, la base de datos Oracle almacena el año como 1987 ó 2004, y no simplemente como 87 ó 04.
Función SYSDATE: es una función de fecha que devuelve la fecha y hora actual del servidor de bases de datos. Puede utilizar SYSDATE igual que cualquier otro nombre de columna. Por ejemplo, puede mostrar la fecha actual seleccionando SYSDATE en una tabla. Se suele seleccionar SYSDATE en una tabla ficticia denominada DUAL.
Ejemplo
SELECT SYSDATE
FROM DUAL;La anterior consulta retorna la fecha y hora del sistema.
Operaciones Aritméticas con Fechas: Como la base de datos almacena fechas como números, puede realizar cálculos mediante operadores aritméticos como la suma o la resta. Puede sumar y restar constantes numéricas además de fechas.
Puede utilizar las siguientes operaciones:
| Operación | Resultado | Descripción |
| fecha + número | Fecha | Suma un número de días a una fecha |
| fecha – número | Fecha | Resta un número de días a una fecha |
| fecha – fecha | Número de días: | Resta una fecha a otra |
| fecha + (número/24) | Fecha | Suma un número de horas a una fecha |
Ejemplo
SELECT last_name,
(SYSDATE-hire_date)/7 AS "Semanas Trabajadas",
SYSDATE+3 "Fecha mas 3 Dias",
SYSDATE-3 "Fecha menos 3 Dias",
SYSDATE-(SYSDATE+6) "Fecha menos (Fecha mas numero de días)"
FROM employees
WHERE department_id = 60;La consulta anterior muestra el nombre de cada empleado del departamento 60, luego a la fecha actual se le resta la fecha de contratación y todo eso se divide por 7 para así determinar el numero de semanas que ha trabajado dicho empleado, la tercera columna muestra la fecha actual mas 3 días, en la 4ta a la misma fecha se le resta 3 días y en la ultima columna a la fecha actual se le resta la (fecha actual mas 6 días). Las ultimas 3 columnas no tienen relación con las 2 primeras. Como notan: si se resta una fecha mas actual a una mas antigua el resultado seria negativo.
Funciones de Fecha
| Función | Resultado |
| MONTHS_BETWEEN | Número de meses entre dos fechas |
| ADD_MONTHS | Agrega meses de calendario a una fecha |
| NEXT_DAY | Día siguiente a la fecha especificada |
| LAST_DAY | Último día del mes |
| ROUND | Redondea la fecha |
| TRUNC | Trunca la fecha |
| EXTRACT | Extrae una porción(DÍA, MES O AÑO) de una fecha dada. |
Las funciones de fechas operan en fechas de Oracle. Todas las funciones de fecha devuelven un valor del tipo de datos DATE excepto MONTHS_BETWEEN y EXTRACT, que devuelven un valor numérico.
• MONTHS_BETWEEN(date1, date2): Busca el número de meses entre date1 y date2. El resultado puede ser positivo o negativo. Si date1 es posterior a date2, el resultado es positivo; si date1 es anterior a date2, el resultado es negativo. La parte no entera del resultado representa una porción del mes.
• ADD_MONTHS(date, n): Agrega un número n de meses de calendario a date. El valor de n debe ser un entero, si n es negativo se le restaría a la fecha dada el n numero de meses.
• NEXT_DAY(date, ‘char’): Busca la fecha del siguiente día de la semana especificado (‘char’) después de date. El valor de char puede ser un número que represente un día o una cadena de caracteres.
• LAST_DAY(date): Busca la fecha del último día del mes que contiene date.
• ROUND(date[,‘fmt’]): Devuelve date redondeado a la unidad especificada por el modelo de formato fmt. Si se omite el modelo de formato fmt, date se redondeará al día más cercano.
• TRUNC(date[, ‘fmt’]): Devuelve date con la porción de tiempo del día truncada a la unidad especificada por el modelo de formato fmt. Si se omite el modelo de formato fmt, date se trunca al día más cercano.
- EXTRACT(porción FROM date): Retorna la porción numérica especificada de date. porción podría ser: MONTH, DAY o YEAR.
Esta lista es un subconjunto de las funciones de fecha disponibles.
Usando las Funciones de Fecha
| Función | Resultado |
| MONTHS_BETWEEN (’21/SEP/2014′,’10/JUL/2012′) | 26.3548387 |
| ADD_MONTHS (’20/APR/2016′,4) | ’20/AUG/16′ |
| NEXT_DAY (’08/MAY/2013′,’THURSDAY’) | ’09/MAY/13′ |
| LAST_DAY (’01/JUN/2016′) | ’30/JUN/16′ |
| SYSDAY=’17/5/2016′ | |
| EXTRACT(DAY FROM SYSDATE) | 17 |
| EXTRACT(MONTH FROM SYSDATE) | 5 |
| EXTRACT(YEAR FROM SYSDATE) | 2016 |
Ejemplo
SELECT employee_id, hire_date,
MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
ADD_MONTHS (hire_date, 6) REVIEW,
NEXT_DAY (hire_date, 'WEDNESDAY'), LAST_DAY(hire_date)
FROM hr.employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) > 36;En el ejemplo anterior mostramos el código de empleado, la fecha de contratación, la cantidad de meses que ha trabajado(MONTHS_BETWEEN (SYSDATE, hire_date)), la fecha de cuando se le hizo su primera revision(ADD_MONTHS (hire_date, 6)), la fecha del primer miércoles después que fueron contratados: NEXT_DAY (hire_date, ‘WEDNESDAY’) y la fecha del último día del mes en cual fueron contratados LAST_DAY(hire_date) de todos lo empleados que tengan mas 36 meses(3 años) en la empresa: MONTHS_BETWEEN (SYSDATE, hire_date) > 36.
Supongamos que SYSDATE: 17-MAY-2016
| Función | Resultado |
| ROUND(SYSDATE,’MONTH’) | 1-JUN-16 |
| ROUND(SYSDATE,’YEAR’) | 1-JAN-16 |
| TRUNC(SYSDATE,’MONTH’) | 1-MAY-16 |
| TRUNC(SYSDATE,’YEAR’) | 1-JAN-16 |
SELECT last_name, hire_date,
ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'YEAR')
FROM employees
WHERE hire_date LIKE '%03'
OR hire_date LIKE'%06';En el anterior ejemplo se muestra el empleado, su fecha de contratación, luego su fecha de contratación es redondeada al mes y por último es truncada al año de todos lo empleados contratados en el 2003 o 2006.