Viernes, 12 Junio 2020 03:47

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

Rate this item
(0 votes)

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 EXTRACT, que devuelven un valor numérico. 

  • MONTHS_BETWEEN(date1,date2):Busca el número de meses entre date1 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 formatofmt. Si se omite el modelo de formato fmtdate 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 fmtdate se trunca al día más cercano. 

EXTRACT(porción FROM date): Retorna la porción numérica especificada de dateporción podría ser: MONTHDAY 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.

Read 1080 times Last modified on Jueves, 20 Agosto 2020 21:26

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.

Magic PL/SQL

Blog orientado al desarrollo de PL/SQL en el "Maravilloso Mundo ORACLE". Cursos Online y Tutoriales Gratis.