DATEDIFF (Transact-SQL)
Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure azure Synapse Analytics Analytics Platform System (PDW)
Эта функция возвращает количество пересеченных границ (целое число со знаком), указанных в аргументе datepart, за период времени, указанный в аргументах startdate и enddate.
Сведения о функции, которая обрабатывает значительные отличия значений аргументов startdate и enddate, см. в статье DATEDIFF_BIG (Transact-SQL). Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).
Соглашения о синтаксисе Transact-SQL
Синтаксис
DATEDIFF ( datepart , startdate , enddate )
Примечание.
Сведения о синтаксисе Transact-SQL для SQL Server 2014 (12.x) и более ранних версиях см . в документации по предыдущим версиям.
Аргументы
datepart
Единицы, в которых функция DATEDIFF сообщает о разнице между startdate и enddate. К часто используемым единицам datepart относятся month
или second
.
Значение datepart не может быть указано в переменной или в виде строки в кавычках, например 'month'
.
В приведенной ниже таблице перечислены все допустимые значения datepart. Функция DATEDIFF принимает либо полное имя datepart, либо любое приведенное сокращение полного имени.
Имя datepart | Сокращение datepart |
---|---|
year | y, yy, yyyy |
quarter | qq, q |
month | mm, m |
dayofyear | dy |
day | dd, d |
week | wk, ww |
weekday | dw, w |
hour | hh |
minute | mi, n |
second | ss, s |
millisecond | ms |
microsecond | mcs |
nanosecond | ns |
Примечание.
Каждое конкретное имя аргумента datepart и сокращение этого имени datepart будут возвращать одно и то же значение.
startdate
Выражение, которое может быть разрешено в одно из следующих значений.
- date
- datetime
- datetimeoffset
- datetime2
- smalldatetime
- time
Во избежание неоднозначности используйте четырехзначную запись года. Сведения о двузначном обозначении года см. в статье Настройка параметра конфигурации сервера two digit year cutoff.
enddate
См. описание аргумента startdate.
Тип возвращаемых данных
int
Возвращаемое значение
Значение типа int, представляющее разницу между аргументами startdate и enddate в границах, определяемых аргументом datepart.
Например, SELECT DATEDIFF(day, '2036-03-01', '2036-02-28');
возвращает значение -2, указывая на то, что 2036 – високосный год. Это означает, что, если начать со startdDate "2036-03-01" и отсчитать -2 дня, будет достигнуто значение enddate "2036-02-28".
В качестве возвращаемого значения вне диапазона для int (от –2 147 483 648 до 2 147 483 647) DATEDIFF
возвращает сообщение об ошибке. Для миллисекунды максимальная разница между startdate и enddate составляет 24 дня 20 часов 31 минута и 23 647 секунд. Для секунды максимальная разница составляет 68 лет, 19 дней, 3 часа, 14 минут и 7 секунд.
Если обоим аргументам, startdate и enddate, присвоено только значение времени, а аргумент datepart не содержит значения времени datepart, то DATEDIFF
возвращает значение 0.
При вычислении возвращаемого значения DATEDIFF
использует компонент смещения часовых поясов для аргументов startdate или enddate.
Так как значение типа smalldatetime имеет точность до минуты, то при использовании в аргументах startdate и enddate значений типа smalldatetime секунды и миллисекунды у возвращаемых значений всегда равны 0.
Если переменной типа данных date присвоено только значение времени, в качестве недостающей части даты DATEDIFF
задает значение по умолчанию: 1900-01-01
. Если переменной типа данных time или date присвоено только значение даты, в качестве недостающей части времени DATEDIFF
задает значение по умолчанию: 00:00:00
. Если в одном из аргументов startdate или enddate указано только время, а в другом только дата, в качестве недостающей информации DATEDIFF
задает значения по умолчанию.
Если аргументы startdate и enddate имеют разные типы данных даты, но при этом один из них имеет больше частей времени или обладает более высокой точностью, DATEDIFF
присваивает значения 0 недостающим частям другого аргумента.
Границы, задаваемые аргументом datepart
Приведенные ниже инструкции имеют одинаковые значения аргументов startdate и enddate. Указанные даты являются соседними, а временная разница между ними составляет 100 наносекунд (0,0000001 секунды). Разница между аргументами startdate и enddate в каждой инструкции пересекает одну календарную или временную границу аргумента datepart. Каждое выражение возвращает значение 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');
Если аргументы startdate и enddate имеют разные значения года, но одинаковые значения календарной недели, DATEDIFF
вернет 0 для части week аргумента datepart.
Замечания
Используйте DATEDIFF
в предложениях SELECT <list>
, WHERE
, HAVING
, GROUP BY
и ORDER BY
.
Функция DATEDIFF
неявно приводит строковые литералы к типу datetime2. Это означает, что DATEDIFF
не поддерживает формат ГЧМ (год, число, месяц) при передаче даты в виде строки. Для использования формата ГЧМ (год, число, месяц) необходимо явно привести строку к типу datetime или smalldatetime.
Указание SET DATEFIRST
не влияет на DATEDIFF
. DATEDIFF
всегда считает воскресенье первым днем недели, чтобы обеспечить детерминизм работы функции.
DATEDIFF
может переполняться с точностью до минуты или более низкой точностью, если разница между enddate и startdate представляет собой значение, выходящее за пределы диапазона, допустимого для int.
Примеры
В этих примерах выражения различного типа используются в качестве аргументов для параметров startdate и enddate.
А. Указание столбцов в качестве начальной и конечной даты
В этом примере подсчитывается количество границ дней, пересекаемых между датами в двух столбцах таблицы.
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. Указание определенных пользователем переменных в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и 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. Указание скалярных системных функций в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают скалярные системные функции.
SELECT DATEDIFF(millisecond, GETDATE(), SYSDATETIME());
D. Указание скалярных вложенных запросов и скалярных функций в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают скалярные вложенные запросы.
USE AdventureWorks2022;
GO
SELECT DATEDIFF(day,
(SELECT MIN(OrderDate) FROM Sales.SalesOrderHeader),
(SELECT MAX(OrderDate) FROM Sales.SalesOrderHeader));
Е. Указание констант в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate используются символьные константы.
SELECT DATEDIFF(day,
'2007-05-07 09:53:01.0376635',
'2007-05-08 09:53:01.0376635');
F. Указание числовых выражений и скалярных системных функций в качестве конечной даты
В этом примере в качестве аргументов для enddate используются числовое выражение (GETDATE() + 1)
и скалярные системные функции GETDATE
и SYSDATETIME
.
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. Указание ранжирующих функций в качестве начальной даты
В этом примере в качестве аргумента 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 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. Указание агрегатной оконной функции в качестве начальной даты
В этом примере в качестве аргумента 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 sod
INNER JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID
WHERE soh.SalesOrderID IN(43659, 58918);
GO
I. Определение разницы между startdate и enddate в виде строковых компонентов даты
-- 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
Результирующий набор:
118 years, 11 months, 11 days, 7 hours, 8 minutes and 1.123 seconds
Примеры: Azure Synapse Analytics и система платформы аналитики (PDW)
В этих примерах выражения различного типа используются в качестве аргументов для параметров startdate и enddate.
J. Указание столбцов в качестве начальной и конечной даты
В этом примере подсчитывается количество границ дней, пересекаемых между датами в двух столбцах таблицы.
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;
-- Returns: 1
K. Указание скалярных вложенных запросов и скалярных функций в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и enddate выступают скалярные вложенные запросы.
-- Uses AdventureWorks
SELECT TOP(1) DATEDIFF(day, (SELECT MIN(HireDate) FROM dbo.DimEmployee),
(SELECT MAX(HireDate) FROM dbo.DimEmployee))
FROM dbo.DimEmployee;
L. Указание констант в качестве начальной и конечной даты
В этом примере в качестве аргументов startdate и 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. Указание ранжирующих функций в качестве начальной даты
В этом примере в качестве аргумента startdate. используется ранжирующая функция.
-- Uses AdventureWorks
SELECT FirstName, LastName,
DATEDIFF(day, ROW_NUMBER() OVER (ORDER BY
DepartmentName), SYSDATETIME()) AS RowNumber
FROM dbo.DimEmployee;
О. Указание агрегатной оконной функции в качестве начальной даты
В этом примере в качестве аргумента startdate используется агрегатная оконная функция.
-- Uses AdventureWorks
SELECT FirstName, LastName, DepartmentName,
DATEDIFF(year, MAX(HireDate)
OVER (PARTITION BY DepartmentName), SYSDATETIME()) AS SomeValue
FROM dbo.DimEmployee