DATEDIFF (Transact-SQL)

Artículo
02/01/2023
Tiempo de lectura: 9 minutos

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Esta función devuelve el recuento (como un valor entero con firma) de los límites datepart que se han cruzado entre los valores startdate y enddate especificados.

Consulte DATEDIFF_BIG (Transact-SQL) para obtener una función que controla las diferencias más importantes entre los valores startdate y enddate. Para obtener una introducción sobre todos los tipos de datos y funciones de fecha y hora de Transact-SQL, vea Tipos de datos y funciones de fecha y hora (Transact-SQL).

Convenciones de sintaxis de Transact-SQL

Sintaxis

DATEDIFF ( datepart , startdate , enddate )

Nota:

Para ver la sintaxis de Transact-SQL para SQL Server 2014 y versiones anteriores, consulte Versiones anteriores de la documentación.

Argumentos

datepart
Las unidades en las que DATEDIFF informa la diferencia entre startdatey enddate. Entre las unidades de datepart usadas comúnmente se incluyen month o second.

No se puede especificar el valor datepart en una variable, ni como una cadena entrecomillada, como 'month'.

En esta tabla se enumeran todos los valores válidos de datepart. DATEDIFF acepta el nombre completo de detepart o cualquier abreviatura indicada del nombre completo.

nombre de datepart	abreviatura de datepart
year	y, yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy
day	dd, d
week	wk, ww
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

Nota

Todos los nombres y las abreviaturas específicas de datepart para ese nombre de datepart devolverán el mismo valor.

startdate
Una expresión que se puede resolver en uno de los valores siguientes:

date
datetime
datetimeoffset
datetime2
smalldatetime
time

Para evitar ambigüedades, use años de cuatro dígitos. Vea Establecer la opción de configuración del servidor Fecha límite de año de dos dígitos para obtener información sobre los valores de año de dos dígitos.

enddate
Vea startdate.

Tipo de valor devuelto

int

Valor devuelto

La diferencia int entre startdate y enddate, expresada en el límite establecido por datepart.

Por ejemplo, SELECT DATEDIFF(day, '2036-03-01', '2036-02-28'); devuelve -2, que indica que 2036 debe ser un año bisiesto. Este caso significa que si comenzamos en startdate "2036-03-01" y, luego, contamos -2 días, alcanzamos endate "2036-02-28".

Para un valor devuelto fuera del intervalo de int (de -2.147.483.648 a +2.147.483.647) DATEDIFF devuelve un error. En millisecond, la diferencia máxima entre startdate y enddate es de 24 días, 20 horas, 31 minutos y 23.647 segundos. Para second, la diferencia máxima es de 68 años, 19 días, 3 horas, 14 minutos y 7 segundos.

Si startdate y enddate solo tienen asignado un valor de hora y datepart no es un valor datepart de hora, DATEDIFF devuelve 0.

DATEDIFF no usa el componente de ajuste de zona horaria de startdate o enddate para calcular el valor devuelto.

Como smalldatetime solo es preciso hasta los minutos, los segundos y milisegundos siempre se establecen en 0 en el valor devuelto cuando startdate o enddate tienen un valor smalldatetime.

Si solo se asigna un valor de hora a una variable de tipo de datos de fecha, DATEDIFF establece el valor de la parte de la fecha que falta en el valor predeterminado: 1900-01-01. Si solo se asigna un valor de fecha a una variable de tipo de datos de fecha u hora, DATEDIFF establece el valor de la parte de la hora que falta en el valor predeterminado: 00:00:00. Si startdate o enddate solo tienen una parte de hora y el otro solo una parte de fecha, DATEDIFF establece las partes de hora y fecha que faltan en los valores predeterminados.

Si startdate y enddate tienen tipos de datos de fecha diferentes y uno tiene más partes de hora o precisión de fracciones de segundo que el otro, DATEDIFF establece las partes que faltan del otro en 0.

Límites de datepart

Las instrucciones siguientes tienen los mismos valores startdate y enddate. Esas fechas son adyacentes y tienen una diferencia horaria de cien nanosegundos (0,0000001 segundos). La diferencia entre startdate y enddate en cada instrucción cruza un límite de calendario u hora de su datepart. Cada instrucción devuelve 1.

SELECT DATEDIFF(year,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(quarter,     '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(month,       '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(dayofyear,   '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(day,         '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(week,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(hour,        '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(minute,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(second,      '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(millisecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');
SELECT DATEDIFF(microsecond, '2005-12-31 23:59:59.9999999', '2006-01-01 00:00:00.0000000');

Si startdate y enddate tienen valores de año diferentes, pero tienen los mismos valores de semana del calendario, DATEDIFF devolverá 0 para datepartweek.

Observaciones

Use DATEDIFF en las cláusulas SELECT <list>, WHERE, HAVING, GROUP BY y ORDER BY.

DATEDIFF convierte implícitamente los literales de cadena como un tipo datetime2. Esto significa que DATEDIFF no admite el formato año-día-mes cuando la fecha se pasa como una cadena. La cadena se debe convertir explícitamente a un tipo datetime o smalldatetime para poder usar el formato año-día-mes.

La especificación de SET DATEFIRST no tiene ningún efecto sobre DATEDIFF. DATEDIFF siempre usa el domingo como el primer día de la semana para garantizar que la función actúa de forma determinista.

DATEDIFF se puede desbordar con una precisión de minute o superior si la diferencia entre enddate y startdate devuelve un valor que está fuera del intervalo para int.

Ejemplos

En estos ejemplos se usan otros tipos de expresiones como argumentos para los parámetros startdate y enddate.

A. Especificar las columnas para startdate y enddate

En este ejemplo se calcula el número de límites de día que se cruzan entre las fechas en dos columnas de una tabla.

CREATE TABLE dbo.Duration  
    (startDate datetime2, endDate datetime2);  
    
INSERT INTO dbo.Duration(startDate, endDate)  
    VALUES ('2007-05-06 12:10:09', '2007-05-07 12:10:09');  
    
SELECT DATEDIFF(day, startDate, endDate) AS 'Duration'  
    FROM dbo.Duration;  
-- Returns: 1

B. Especificar las variables definidas por el usuario para startdate y enddate

En este ejemplo, las variables definidas por el usuario actúan como argumentos para startdate y enddate.

DECLARE @startdate DATETIME2 = '2007-05-05 12:10:09.3312722';  
DECLARE @enddate   DATETIME2 = '2007-05-04 12:10:09.3312722';   
SELECT DATEDIFF(day, @startdate, @enddate);

C. Especificar las funciones de sistema escalares para startdate y enddate

En este ejemplo se usan las funciones de sistema escalares como argumentos para startdate y enddate.

SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());

D. Especificar las funciones escalares y de subconsulta para startdate y enddate

En este ejemplo se usan las funciones escalares y de subconsulta como argumentos para startdate y enddate.

USE AdventureWorks2012;  
GO  
SELECT DATEDIFF(day,
    (SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader),  
    (SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader));

E. Especificar las constantes para startdate y enddate

En este ejemplo se usan constantes de caracteres como argumentos para startdate y enddate.

SELECT DATEDIFF(day,
   '2007-05-07 09:53:01.0376635',
   '2007-05-08 09:53:01.0376635');

F. Especificar expresiones numéricas y funciones de sistema escalares para enddate

En este ejemplo se usa una expresión numérica, (GETDATE() + 1), y las funciones de sistema escalares GETDATE y SYSDATETIME, como argumentos para enddate.

USE AdventureWorks2012;  
GO  
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1)
    AS NumberOfDays  
    FROM Sales.SalesOrderHeader;  
GO  
USE AdventureWorks2012;  
GO  
SELECT
    DATEDIFF(
            day,
            '2007-05-07 09:53:01.0376635',
            DATEADD(day, 1, SYSDATETIME())
        ) AS NumberOfDays  
    FROM Sales.SalesOrderHeader;  
GO

G. Especificar las funciones de clasificación para startdate

En este ejemplo se usa una función de categoría como argumento para startdate.

USE AdventureWorks2012;  
GO  
SELECT p.FirstName, p.LastName  
    ,DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY   
        a.PostalCode), SYSDATETIME()) AS 'Row Number'  
FROM Sales.SalesPerson s   
    INNER JOIN Person.Person p   
        ON s.BusinessEntityID = p.BusinessEntityID  
    INNER JOIN Person.Address a   
        ON a.AddressID = p.BusinessEntityID  
WHERE TerritoryID IS NOT NULL   
    AND SalesYTD <> 0;

H. Especificar una función de ventana agregada para startdate

En este ejemplo se usa una función de ventana agregada como argumento para startdate.

USE AdventureWorks2012;  
GO  
SELECT soh.SalesOrderID, sod.ProductID, sod.OrderQty, soh.OrderDate,
    DATEDIFF(day, MIN(soh.OrderDate)   
        OVER(PARTITION BY soh.SalesOrderID), SYSDATETIME()) AS 'Total'  
FROM Sales.SalesOrderDetail sod  
    INNER JOIN Sales.SalesOrderHeader soh  
        ON sod.SalesOrderID = soh.SalesOrderID  
WHERE soh.SalesOrderID IN(43659, 58918);  
GO

I. Búsqueda de la diferencia entre startdate y enddate como cadenas de partes de fecha

-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 DATETIME, @date2 DATETIME, @result VARCHAR(100);
DECLARE @years INT, @months INT, @days INT,
    @hours INT, @minutes INT, @seconds INT, @milliseconds INT;

SET @date1 = '1900-01-01 00:00:00.000'
SET @date2 = '2018-12-12 07:08:01.123'

SELECT @years = DATEDIFF(yy, @date1, @date2)
IF DATEADD(yy, -@years, @date2) < @date1 
SELECT @years = @years-1
SET @date2 = DATEADD(yy, -@years, @date2)

SELECT @months = DATEDIFF(mm, @date1, @date2)
IF DATEADD(mm, -@months, @date2) < @date1 
SELECT @months=@months-1
SET @date2= DATEADD(mm, -@months, @date2)

SELECT @days=DATEDIFF(dd, @date1, @date2)
IF DATEADD(dd, -@days, @date2) < @date1 
SELECT @days=@days-1
SET @date2= DATEADD(dd, -@days, @date2)

SELECT @hours=DATEDIFF(hh, @date1, @date2)
IF DATEADD(hh, -@hours, @date2) < @date1 
SELECT @hours=@hours-1
SET @date2= DATEADD(hh, -@hours, @date2)

SELECT @minutes=DATEDIFF(mi, @date1, @date2)
IF DATEADD(mi, -@minutes, @date2) < @date1 
SELECT @minutes=@minutes-1
SET @date2= DATEADD(mi, -@minutes, @date2)

SELECT @seconds=DATEDIFF(s, @date1, @date2)
IF DATEADD(s, -@seconds, @date2) < @date1 
SELECT @seconds=@seconds-1
SET @date2= DATEADD(s, -@seconds, @date2)

SELECT @milliseconds=DATEDIFF(ms, @date1, @date2)

SELECT @result= ISNULL(CAST(NULLIF(@years,0) AS VARCHAR(10)) + ' years,','')
     + ISNULL(' ' + CAST(NULLIF(@months,0) AS VARCHAR(10)) + ' months,','')    
     + ISNULL(' ' + CAST(NULLIF(@days,0) AS VARCHAR(10)) + ' days,','')
     + ISNULL(' ' + CAST(NULLIF(@hours,0) AS VARCHAR(10)) + ' hours,','')
     + ISNULL(' ' + CAST(@minutes AS VARCHAR(10)) + ' minutes and','')
     + ISNULL(' ' + CAST(@seconds AS VARCHAR(10)) 
     + CASE
            WHEN @milliseconds > 0
                THEN '.' + CAST(@milliseconds AS VARCHAR(10)) 
            ELSE ''
       END 
     + ' seconds','')

SELECT @result

El conjunto de resultados es el siguiente:

118 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds

Ejemplos: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)