Por: Jim Evans | Actualizado: 2020-07-16 | Comentarios (1) | Relacionado: Más > Fechas
Problema
A menudo, necesitamos calcular la diferencia entre dos fechas y devolver los resultadosen una parte de fecha deseada o un incremento como días, horas, minutos. Afortunadamente, SQLServer proporciona una función para esto.
En este artículo mostraré las funciones SQL DATEDIFF y DATEDIFF_BIG y compartiré varios ejemplos de cómo usar cada una. También le mostraré las limitaciones y cómo trabajar alrededor de ellos. También aprenderá a calcular la edad de la ciudad de San Agustín en nanosegundos.
Solución
Exploraremos las funciones DATEDIFF y DATEDIFF_BIG, mostraremos cómo se utilizan y proporcionaremos varios ejemplos.
Qué es la función DATEDIFF de SQL Server
DATEDIFF() es una función básica de SQL Server que se puede utilizar para hacer cálculos de fechas. Específicamente, obtiene la diferencia entre 2 fechas con los resultados devueltos en unidades de fecha especificadas como años, meses días, minutos, segundos como un valor int (entero).
Sintaxis:
DATEDIFF( DatePart, StartDate, EndDate )
Qué es la función DATEDIFF_BIG de SQL Server
DATEDIFF_BIG() es una función SQL que se introdujo en SQL Server 2016. Se puede utilizarpara hacer cálculos de fechas también. En concreto, obtiene la diferencia entre 2 fechascon los resultados devueltos en unidades de fecha especificadas como años, meses días, minutos,segundos como un valor bigint.
Sintaxis:
DATEDIFF_BIG( DatePart, StartDate, EndDate )
Cómo usar DATEDIFF y DATEDIFF_BIG
El uso de DATEDIFF y DATEDIFF_BIG es el mismo: DATEDIFF(datepart of returnvalues, Start Date, End Date).
Ejemplos:
SELECT DATEDIFF( MILLISECOND, '07-04-2020', '07-05-2020') --> = 86400000 SELECT DATEDIFF_BIG( NANOSECOND, '07-04-2020', '07-05-2020') --> = 86400000000000
Primer parámetro: es un datepartargument válido que es uno de los siguientes desde nanosegundo hasta año:
| Nombre de la parte de fecha | Abreviatura |
|---|---|
| año | año, aaaa |
| trimestre | q, q |
| mes | mm, m |
| día del año | dy, y |
| día | dd, d |
| semana | wk, ww |
| hora | hh |
| minuto | mi, n |
| segundo | ss, s |
| milisegundo | ms | microsegundo | mcs | nanosegundo | ns |
Parámetro Segundo: es la fecha de inicio. Una fecha válida, datetime, datetimeoffset, datetime2, smalldatetime, o time data typevariable o una cadena que se resolvió a un datetime datatype.
Tercer Parámetro: es la Fecha de Finalización. Una variable de tipo validdate, datetime, datetimeoffset, datetime2, smalldatetime, o time o una cadena que resuelva a un tipo de dato datetime.
*Esta información se puede encontrar en Microsoft Docs!
Ejemplos de DATEDIFF de SQL Server
A continuación se muestran ejemplos básicos utilizando los argumentos de datapart más comunes.
| Ejemplo | Código DATEDIFF de SQL Server | Salida |
|---|---|---|
| ¿Cuántos minutos tiene un día? | SELECT DATEDIFF(MINUTE, ’07-04-2020′, ’07-05-2020′) | 1440 minutos | ¿Cuántas horas tiene un día? | SELECT DATEDIFF(HOUR, ’01-01-2020′, ’01-02-2020′) | 24 Horas | ¿Cuántos días tiene un año? | SELECT DATEDIFF(DAY, ’01-01-2020′, ’12-31-2020′) | 365 Días | ¿Cuántos meses tiene un año? | SELECCIONE DATEDIFF(MES, ’01-01-2019′, ’01-01-2020′) | 12 Meses |
| ¿Cuántos años desde el 2000 al 2020? | SELECT DATEDIFF(YEAR, ’01-01-2000′, ’01-01-2020′) | 20 Años |
Cuándo utilizar la función DATEDIFF_BIG de SQL Server
Utilice la función DATEDIFF_BIG cuando sus resultados superen el rango de un valor entero que esté entre (-2.147.483.648 a +2.147.483.647).
BIGINT tiene un rango de (-9,223,372,036,854,775,808 a 9,223,372,036,854,775,807) ¡Parece que sería difícil superar este rango! Pero vamos a presentar un ejemplo que supera este rango cuando se utiliza DATEDIFF_BIG.
SQL Server DATEDIFF_BIG en lugar de DATEDIFF
¿Cómo superar el valor de retorno de DATEDIFF int? Microsoft Docs proporciona 2 ejemplos de cómo desbordar el valor de retorno del entero DATEDIFF que demuestro a continuación y mostrar cómo DATEDIFF_BIG funciona para evitar la limitación.
--1. For millisecond, max difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. -- the following will exceed this range.SELECT DATEDIFF(MILLISECOND, '01-01-2020', '02-01-2020')
Esto resulta en el siguiente error.
La función datediff resultó en un desbordamiento. El número de dateparts que separa dos instancias de fecha/hora es toolarge. Intente usar datediff con una parte de fecha menos precisa.
Aquí hay otro ejemplo.
--2. For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds. -- the following will exceed this range.SELECT DATEDIFF(SECOND, '01-01-1950', '02-01-2020')
Esto da como resultado el siguiente error.
La función datediff dio como resultado un desbordamiento. El número de dateparts que separa dos instancias de fecha/hora es toolarge. Intente utilizar datediff con un datepart menos preciso.
Utilice DATEDIFF_BIG en su lugar para evitar los errores anteriores.
--3. How to get around the integer limit using DATEDIFF_BIG functionSELECT DATEDIFF_BIG(MILLISECOND, '01-01-2020', '02-01-2020') --> = 2678400000SELECT DATEDIFF_BIG(SECOND, '01-01-1950', '02-01-2020') –-> = 2211667200
Cuando DATEDIFF_BIG no es lo suficientemente grande
De acuerdo con Microsoft Docs, DATEDIFF_BIG sólo puede desbordarse si se utiliza nanosecondprecision donde la diferencia entre enddate y startdate es más de 292 años,3 meses, 10 días, 23 horas, 47 minutos y 16.8547758 segundos.
Esto podría ser un problema si quieres saber cuántos nanosegundos han pasado desde que se fundó la ciudad más antigua de América, San Agustín, Florida.
--1. How many nanoseconds since St. Augustine was founded on Sept. 8, 1565?SELECT DATEDIFF_BIG(NANOSECOND, '09-08-1565', GETDATE())
¡Caramba! Obtenemos un error.
La función datediff_big resultó en un desbordamiento. El número de partes de fecha que separan dos instancias de fecha/hora es toolarge. Intente usar datediff_big con una parte de fecha menos precisa.
Intentemos esto en múltiples pasos:
--1. First get the number of Years since St. Augustine was foundedSELECT DATEDIFF_BIG(YEAR, '09-08-1565', GETDATE()) --> = 455 --2. Next, get the number of nanoseconds in a yearSELECT DATEDIFF_BIG(NANOSECOND, '01-01-2020 00:00:00.0000000', '12-31-2020 23:59:59.9999999') --> = 31622399999999900--3. Last multiply the results together and cast to decimal(38,0)-- 455 x 31622399999999900 = 14388191999999954500SELECT CAST(455 * 31622399999999900 as DECIMAL(38,0)) --> = 14388191999999954500
¡Walla! Ahora sabemos cuántos nanosegundos han pasado desde la fundación de San Agustín.
Resumen
Espero que hayas disfrutado de este ejercicio sobre DATEDIFF y DATEDIFF_BIG. DATEDIFF es una función SQL de uso común que ha existido durante muchos años. DATEDIFF_BIG es una función relativamente nueva que se introdujo en SQL Server 2016. Aunque he proporcionado ejemplos de desbordamiento de estas funciones, es raro que te encuentres con estos escenarios. ¡Estas son funciones útiles que deben ser añadidas a su caja de herramientas de SQL!
Siguientes pasos
- Aprovecha otros artículos sobre Datediff
- Lee sobre -work arounds para calcular la edad
- Lee sobre otras funciones SQL interesantes -LAG y LEAD
- Encuentra otros artículos sobre Datetime -MS SQL Tips Search on DateDiff
- Más consejos para desarrolladores de bases de datos…
Última actualización: 2020-07-16
Acerca del autor
Jim Evans es un Director de TI actualmente en Harsco que ha gestionado DBAs, Desarrolladores de aplicaciones y BI y equipos de gestión de datos durante más de 20 años.Ver todos mis consejos