Uso de los tipos de datos y funciones de fecha y hora en el servidor SQL

Las operaciones más comunes con datos de fecha y hora corresponden a una de las siguientes categorías:

1. Determinar si una expresión es una fecha u hora válida.
2. Vea la fecha y la hora actual.
3. Dada una fecha y hora, devuelva el año, el mes, el día o el día de la semana en forma de una cadena o un número entero (por ejemplo, octubre o el 10 representan el mismo mes).
4. Construye horas y/o fechas usando partes individuales de horas, minutos, segundos, días, meses y años.
5. Sume (o reste) partes de fecha/hora de una fecha determinada.
6. Calcula la diferencia entre dos fechas u horas.

Discutamos e ilustremos lo anterior con más detalle a través de ejemplos.

Ejemplo 0: La expresión es una fecha u hora válida

Antes de que se procese una expresión que se supone que es una fecha o una hora, se puede utilizar la función ISDATE() para determinar su validez. ISDATE() devuelve 1 si la expresión es una fecha/hora válida o 0 en caso contrario, como se puede ver en la Fig. 1:

12345678SELECT ISDATE($00272019-08-24$0027)AS A, ISDATE($00272019-08-24 13:35$0027)AS B, ISDATE($00272019-08-24 13:35:12$0027)AS C, ISDATE($002712:35$0027)AS D, ISDATE($00272019-08-32$0027)AS E, ISDATE($002712:61$0027)AS F, ISDATE($002725:12$0027)AS G

sql

En este caso, de la A a la D son 1 porque:

• 2019-08-24 es una fecha válida.
• 2019-08-24 13:35 es una fecha válida con datos de hora y minutos. Lo mismo se aplica al 2019-08-24 13:35:12, que también incluye los segundos.

Por otro lado, de la E a la G son 0 porque:

• El mes de agosto no tiene 32 días, lo que hace que el 2019-08-32 sea una fecha inválida.
• Los días tienen 24 horas y cada hora tiene 60 minutos, lo que hace que los tiempos de 25:12 y 12:61 sean inválidos.

Además de las cadenas de caracteres, ISDATE() admite, como argumento, otras funciones de fecha y hora, como explicaremos más adelante.

Ejemplo 1: Ver la fecha y la hora actual

Para ver la fecha y la hora actuales, puede utilizar GETDATE() o CURRENT_TIMESTAMP con los mismos resultados que se muestran en la Fig. 2:

12SELECT GETDATE()AS[Using GETDATE()]SELECTCURRENT_TIMESTAMPAS[UsingCurrent_TIMESTAMP]

sql

Cabe señalar que GETDATE() es específico del servidor SQL, mientras que CURRENT_TIMESTAMP forma parte del estándar ANSI SQL y, por lo tanto, es portátil en otros sistemas de bases de datos.

Ejemplo 2: Devolver las partes de fecha y hora

Se dispone de varias funciones para extraer partes de fecha y hora de expresiones válidas de fecha/hora:

• Si está interesado en convertir los meses y días de la semana a sus nombres correspondientes, use DATENAME() de la siguiente manera:

1234567SELECT DATENAME(MONTH,$00272019-08-24$0027)AS[Monthusing DATENAME], DATENAME(YEAR,$00272019-08-24$0027)AS[Yearusing DATENAME], DATENAME(DAY,$00272019-08-24$0027)AS[Dayusing DATENAME], DATENAME(WEEKDAY,$00272019-08-24$0027)AS[Día de la semana usando DATENAME], DATENAME(DAYOFYEAR,$00272019-08-24$0027)AS[Día del año usando DATENAME], DATENAME(QUARTER,$00272019-08-24$0027)AS[Trimestre usando DATENAME]

sql

• En caso de que los equivalentes enteros sean más adecuados para sus necesidades, DATEPART() podría ser más apropiado:

1234567SELECT DATEPART(MES,$00272019-08-24$0027)AS[Mes usando DATEPART], DATEPART(AÑO,$00272019-08-24$0027)AS[Año usando DATEPART], DATEPART(DÍA,$00272019-08-24$0027)AS[Día usando DATEPART], DATEPART(WEEKDAY,$00272019-08-24$0027)AS[Día de la semana usando DATEPART], DATEPART(DAYOFYEAR,$00272019-08-24$0027)AS[Día del año usando DATEPART], DATEPART(QUARTER,$00272019-08-24$0027)AS[Trimestre usando DATEPART]

sql

La Fig. 3 muestra la salida de los dos conjuntos de consulta anteriores:

Además de DATENAME() y DATEPART(), puede usar alternativamente DAY(), MONTH(), o YEAR() para obtener la representación entera del día, mes y año de una fecha dada que se pasa como parámetro.

Ejemplo 3: Horas y fechas de construcción de las partes individuales

Para formar fechas y horas utilizando partes individuales (milisegundos, segundos, minutos, horas, días, meses y años), las funciones DATEFROMPARTS() y DATETIMEFROMPARTS() pueden utilizarse como se ilustra en la Fig. 4:

12345SELECT DATEFROMPARTS(1989,12,2)AS FirstDate, DATEFROMPARTS(2003,11,25)AS SecondDate, DATETIMEFROMPARTS(2019,8,24,19,23,12,0)AS ThirdDate, DATETIMEFROMPARTS(2019,8,25,0,12,32,123)AS FourthDate

sql

La imagen de arriba muestra que en ambas funciones, el primer argumento es el año, el segundo es el mes, el tercero es el da,y y así sucesivamente. Mientras que DATEFROMPARTS() requiere sólo esos tres, DATETIMEFROMPARTS() también necesita los datos de milisegundos, segundos, minutos y horas.

Ejemplo 4: Sumar o restar partes de fecha y hora de las fechas

Dada una fecha u hora, puede ser necesario sumar o restar un número de minutos, horas o días, para nombrar algunos ejemplos. Para lograr este objetivo, DATEADD() puede ser útil para abordar varios casos como se ve en la Fig. 5.

1234SELECCIONE LA FECHA ADICIONAL (DIA,30,$00272019-02-14$0027)COMO FACTURA VENCIDA, FECHA ADICIONAL (SEMANA,-1, GETDATE())COMO UNA SEMANA ANTES, FECHA ADICIONAL (MINUTO,45, GETDATE())COMO CUARENTA Y CINCO MINUTOS DESDE AHORA

sql

Como se demuestra en este ejemplo, escenarios comunes como

• ¿Cuándo vence la factura si la fecha de entrega fue el día de San Valentín?
• ¿Cuál fue la fecha exacta hace una semana?
• ¿Qué hora será dentro de 45 minutos?

puede ser manejado fácilmente usando DATEADD() donde el primer parámetro es la parte de la fecha o la hora, el segundo es el número a sumar o restar, y el último es una expresión de fecha/hora. En cualquier caso, la función siempre devuelve un valor de fecha/hora.

A veces, puede ser de interés regresar el último día del mes actual, el anterior o el siguiente. Para ello, puede emplear EOMONTH() con una fecha y un número opcional de meses para sumar o restar a esa fecha:

1234SELECCIONE EL MES (GETDATE())COMO EL ÚLTIMO DÍA DEL MES ACTUAL, EL MES (GETDATE(),-1)COMO EL ÚLTIMO DÍA DEL ÚLTIMO MES, EL MES (GETDATE(),1)COMO EL ÚLTIMO DÍA DEL PRÓXIMO MES

sql

La Fig. 6 muestra el resultado de las consultas anteriores. Como puede ver, EOMONTH() siempre devuelve una fecha.

Ejemplo 5: Calcular las diferencias entre fechas y horas

Si necesita saber cuántos días han pasado desde una fecha determinada, T-SQL proporciona DATEDIFF(). Por supuesto, esta función también puede devolver otras partes de la fecha o la hora, no sólo días.

Construyamos sobre algunas de las funciones que hemos discutido hasta ahora. Lea las siguientes preguntas y mire la Fig. 7 para entender lo que hacen antes de proceder a la explicación de abajo:

12345SELECT DATEDIFF(DAY, GETDATE(), EOMONTH(GETDATE()))AS H, DATEDIFF(DAY, GETDATE(), DATEADD(MINUTE,3000, GETDATE()))AS I, DATEDIFF(HOUR,$00272019-01-01 00: 00:00$0027, GETDATE())AS J, DATEDIFF(SEGUNDO,$00272019-08-02 07:30:00$0027,$00272019-08-10 19:30:00$0027)AS K

sql

• H nos dice cuántos días faltan para el final del mes actual.
• Represento el número de días entre ahora y ahora + 3000 minutos.
• J muestra el total de horas que han transcurrido desde el comienzo de 2019.
• K es el número de segundos entre el 2 de agosto 07:30:00 am y el 10 de agosto 07:30:00 pm.

Recuerde que sus resultados serán diferentes según el momento en que ejecute estos ejemplos, a menos que tenga una máquina del tiempo y pueda volver a la fecha exacta en la que estaba escribiendo esta guía.

Poniendo todo junto: Usando la base de datos de Northwind

Usando las tablas Pedidos , Clientes , y Empleados , exploremos cómo recuperar la siguiente información:

• ¿Cuántos pedidos se enviaron en dos días?

1234SELECTCOUNT (OrderId)AS ENVIADO A TIEMPO DESDE LAS ORDENES DONDE DIFF(DIA, FECHA DE ORDEN, FECHA DE ENVIO)<=2;

sql

• ¿Qué empleados fueron contratados hace más de 26 años hasta el último día del mes actual?

12345SELECT CONCAT(Nombre,$0027 $0027, Apellido)AS Employee, HireDateFROM EmployeesWHERE HireDate < DATEADD(AÑO,-26, EOMONTH(GETDATE()))

sql

• Históricamente, ¿cuáles son los meses con mayor y menor número de pedidos? Como pueden ver en la figura 8, abril parece ser el mejor mes del año, mientras que junio muestra una drástica disminución en las ventas:

123456SELECCIONE EL NOMBRE DE LA FECHA (MES, FECHA DE LA ORDEN) COMO [MES], CUENTA [ORDEN ID] COMO [CONTEO DE ORDEN POR MES] DE LAS ORDENESNOMBRE DEL GRUPO (MES, FECHA DE LA ORDEN) CONTEO DE ORDEN (ORDEN ID) DESC

sql

Estos ejemplos (y otros innumerables en los que se puede pensar) ponen de relieve la importancia de utilizar las funciones de fecha y hora en el servidor SQL para facilitar el desarrollo e impulsar la toma de decisiones.