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
DATEDIFF ( datepart , startdate , enddate )
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. DATEDIFF
acepta 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.
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.
Vea startdate.
int
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-01
y, 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
.
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
.
Use DATEDIFF
en las SELECT <list>
cláusulas , WHERE
, HAVING
, GROUP BY
y 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.
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.
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
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);
En este ejemplo se usan las funciones de sistema escalares como argumentos para startdate y enddate.
SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());
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));
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');
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
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;
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
-- 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
En estos ejemplos se usan otros tipos de expresiones como argumentos para los parámetros 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
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;
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;
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;
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;