Las Fechas y sus Funciones-(Funciones de una Fila SQL, Parte 2)

Objetivos: 

•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

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:

Muestre la fecha actual mediante la tabla DUAL. 

SELECT 

            SYSDATE

FROM DUAL;

--OUTPUT: 16/05/2016 01:46:28 p.m. -- Mi fecha y hora.

--

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;

/* En el pasado ejemplo se 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

Ejemplo:

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.*/

_____________________________________________________________________________________

A continuación la ultima parte de Funciones de una Fila:

======>>Funciones de Conversión.

Fuente: Base de Datos Oracle 10g: Conceptos Fundamentales de SQL 1.