DATEDIFF (Transact-SQL)

Статья
04/26/2023

Применимо к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Azure Synapse Analytics AnalyticsPlatform 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)