DATEDIFF (Transact-SQL)
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 para obtener una función que controle las diferencias más grandes entre los valores startdate y enddate . Consulte Tipos y funciones de datos de fecha y hora para obtener información general sobre todos los tipos de datos y funciones de fecha y hora de Transact-SQL.
Convenciones de sintaxis de Transact-SQL
Sintaxis
DATEDIFF ( datepart , startdate , enddate )
Argumentos
datepart
Especifica las unidades en las que DATEDIFF se notifica la diferencia entre startdate y enddate. Entre las unidades de datepart usadas comúnmente se incluyen month o second.
El valor datepart no se puede especificar en una variable ni como una cadena entre comillas como 'month'.
En esta tabla se enumeran todos los valores válidos de datepart. DATEDIFFacepta el nombre completo de datepart o cualquier abreviatura enumerada del nombre completo.
| nombre de datepart | abreviatura de datepart |
|---|---|
year |
yy, yyyy |
quarter |
qq, q |
month |
mm, m |
dayofyear |
dy, y |
day |
dd, d |
week |
wk, ww |
weekday |
dw, w |
hour |
hh |
minute |
mi, n |
second |
ss, s |
millisecond |
ms |
microsecond |
mcs |
nanosecond |
ns |
Cada nombre y abreviaturas de datepart específicos para ese nombre datepart devuelve 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. Consulte Configuración del servidor: 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.
Tipos de valores devueltos
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 empezamos en startdate 2036-03-01y, a continuación, contar -2 días, llegamos a la fecha de finalización de 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. Para 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 se asignan solo a un valor de hora y datepart no es una fechapart de hora, DATEDIFF devuelve 0.
DATEDIFF no usa el componente de ajuste de zona horaria de startdate o enddate para calcular el valor devuelto.
Dado que smalldatetime es preciso solo para el minuto, los segundos y milisegundos siempre se establecen 0 en 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 tiempo o precisión de fracciones de segundos que la otra, 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(weekday, '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 natural, DATEDIFF devuelve 0 para datepart week.
Comentarios
Use DATEDIFF en las SELECT <list>cláusulas , WHERE, HAVING, GROUP BYy ORDER BY .
DATEDIFF convierte implícitamente los literales de cadena como un tipo datetime2. En otras palabras, DATEDIFF no admite el formato YDM cuando se pasa la fecha como una cadena. Debe convertir explícitamente la cadena en un tipo datetime o smalldatetime para usar el YDM formato .
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 puede desbordar con una precisión de minute o superior, si la diferencia entre enddate e startdate devuelve un valor que está fuera del intervalo para int.
Ejemplos
Los ejemplos de código de Transact-SQL de este artículo utilizan la base de datos de ejemplo AdventureWorks2022 o AdventureWorksDW2022, que se pueden descargar desde la página principal de Ejemplos y proyectos de la comunidad de Microsoft SQL Server.
En estos ejemplos se usan otros tipos de expresiones como argumentos para los parámetros startdate y enddate.
A Especificar 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;
Este es el conjunto de resultados.
Duration
--------
1
B. Especificar 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 AS DATETIME2 = '2007-05-05 12:10:09.3312722';
DECLARE @enddate AS DATETIME2 = '2007-05-04 12:10:09.3312722';
SELECT DATEDIFF(day, @startdate, @enddate);
C. Especificación de funciones escalares del sistema 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. Especificación de subconsultas escalares y funciones escalares para startdate y enddate
En este ejemplo se usan las funciones escalares y de subconsulta como argumentos para startdate y enddate.
USE AdventureWorks2022;
GO
SELECT DATEDIFF(day, (SELECT MIN(OrderDate)
FROM Sales.SalesOrderHeader), (SELECT MAX(OrderDate)
FROM Sales.SalesOrderHeader));
E. Especificar 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 del 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 AdventureWorks2022;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', GETDATE() + 1) AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO
USE AdventureWorks2022;
GO
SELECT DATEDIFF(day, '2007-05-07 09:53:01.0376635', DATEADD(day, 1, SYSDATETIME())) AS NumberOfDays
FROM Sales.SalesOrderHeader;
GO
G. Especificación de funciones de clasificación para startdate
En este ejemplo se usa una función de categoría como argumento para startdate.
USE AdventureWorks2022;
GO
SELECT p.FirstName,
p.LastName,
DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY a.PostalCode), SYSDATETIME()) AS 'Row Number'
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
H. Especificación de una función de ventana de agregado para startdate
En este ejemplo se usa una función de ventana agregada como argumento para startdate.
USE AdventureWorks2022;
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 AS sod
INNER JOIN Sales.SalesOrderHeader AS soh
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN (43659, 58918);
GO
I. Buscar diferencia entre startdate y enddate como cadenas de elementos de fecha
-- DOES NOT ACCOUNT FOR LEAP YEARS
DECLARE @date1 AS DATETIME, @date2 AS DATETIME, @result AS VARCHAR (100);
DECLARE @years AS INT, @months AS INT, @days AS INT, @hours AS INT, @minutes AS INT, @seconds AS INT, @milliseconds AS 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;
Este es el conjunto de resultados.
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)
En estos ejemplos se usan otros tipos de expresiones como argumentos para los parámetros startdate y enddate.
J. Especificar 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 TOP (1) DATEDIFF(day, startDate, endDate) AS [Duration]
FROM dbo.Duration;
Duration
--------
1
K. Especificación de subconsultas escalares y funciones escalares para startdate y enddate
En este ejemplo se usan las funciones escalares y de subconsulta como argumentos para startdate y enddate.
-- Uses AdventureWorks
SELECT TOP (1) DATEDIFF(day, (SELECT MIN(HireDate)
FROM dbo.DimEmployee), (SELECT MAX(HireDate)
FROM dbo.DimEmployee))
FROM dbo.DimEmployee;
L. Especificar constantes para startdate y enddate
En este ejemplo se usan constantes de caracteres como argumentos para startdate y enddate.
-- Uses AdventureWorks
SELECT TOP (1) DATEDIFF(day, '2007-05-07 09:53:01.0376635', '2007-05-08 09:53:01.0376635')
FROM DimCustomer;
M. Especificación de funciones de clasificación para startdate
En este ejemplo se usa una función de categoría como argumento para startdate.
-- Uses AdventureWorks
SELECT FirstName,
LastName,
DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY DepartmentName), SYSDATETIME()) AS RowNumber
FROM dbo.DimEmployee;
Hora Especificación de una función de ventana de agregado para startdate
En este ejemplo se usa una función de ventana agregada como argumento para startdate.
-- Uses AdventureWorks
SELECT FirstName,
LastName,
DepartmentName,
DATEDIFF(year, MAX(HireDate) OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue
FROM dbo.DimEmployee;