DATEDIFF_BIG (Transact-SQL)

Относится к: SQL Server 2016 (13.x) и более поздние версии База данных SQL Azure AzureSQL Managed InstanceSQL analytics endpoint in Microsoft FabricWarehouse in Microsoft Fabric SQLdatabase in Microsoft Fabric

Эта функция возвращает количество пересеченных границ (целое число со знаком), указанных в аргументе datepart, за период времени, указанный в аргументах startdate и enddate.

Обзор всех типов данных и функций даты и времени в языке Transact-SQL см. в статье Типы данных и функции даты и времени (Transact-SQL).

Соглашения о синтаксисе Transact-SQL

Синтаксис

DATEDIFF_BIG ( datepart , startdate , enddate )  

Аргументы

datepart
Часть аргументов startdate и enddate, которая задает тип пересекаемых границ.

Примечание.

DATEDIFF_BIG не принимает значения datepart из переменных, определяемых пользователем, или как строки в кавычках.

В приведенной ниже таблице перечислены все допустимые имена аргументов datepart и их сокращения.

Имя datepart	Сокращение datepart
year	yy, yyy
quarter	qq, q
month	мм, м
dayofyear	dy, y
day	dd, d
week	wk, ww
hour	hh
minute	Ми, Н
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

Примечание.

Каждое конкретное имя аргумента datepart и сокращение этого имени datepart будут возвращать одно и то же значение.

startdate
Выражение, которое может быть разрешено в одно из следующих значений.

• date
• datetime
• datetimeoffset
• datetime2
• smalldatetime
• time

Для dateDATEDIFF_BIG будет принимать столбец выражения, выражение, строковый литерал или определяемую пользователем переменную. Значение строкового литерала должно разрешаться в datetime. Во избежание неоднозначности используйте четырехзначную запись года. DATEDIFF_BIG вычитает startdate из enddate. Во избежание неоднозначности используйте четырехзначную запись года. Сведения о двузначном обозначении года см. в статье Настройка параметра конфигурации сервера two digit year cutoff.

enddate
См. описание аргумента startdate.

Тип возвращаемых данных

bigint со знаком

Возвращаемое значение

Значение типа bigint, представляющее разницу между аргументами startdate и enddate в границах, определяемых аргументом datepart.

В качестве возвращаемого значения вне диапазона для bigint (от –9 223 372 036 854 775 808 до 9 223 372 036 854 775 807) DATEDIFF_BIG возвращает сообщение об ошибке. В отличие от функции, которая возвращает значение типа int и поэтому может переполняться с точностью до минуты или более высокой точностью, DATEDIFF_BIG может переполняться только при использовании точности до наносекунды, если разница между enddate и startdate больше 292 лет, 3 месяцев, 10 дней, 23 часов, 47 минут и 16,8547758 секунд.

Если обоим аргументам, startdate и enddate, присвоено только значение времени, а аргумент datepart не содержит значения времени datepart, то DATEDIFF_BIG возвращает значение 0.

При вычислении возвращаемого значения DATEDIFF_BIG учитывает компонент смещения часовых поясов для аргументов startdate или enddate.

Так как значение типа smalldatetime имеет точность до минуты, то при использовании в аргументах startdate или enddateDATEDIFF_BIG всегда задает 0 в качестве возвращаемого значения smalldatetime секунд и миллисекунд.

Если переменной типа данных date присвоено только значение времени, в качестве недостающей части даты DATEDIFF_BIG задает значение по умолчанию: 1900-01-01. Если переменной типа данных time или date присвоено только значение даты, в качестве недостающей части времени DATEDIFF_BIG задает значение по умолчанию: 00:00:00. Если в одном из аргументов startdate или enddate указано только время, а в другом только дата, в качестве недостающей информации DATEDIFF_BIG задает значения по умолчанию.

Если аргументы startdate и enddate имеют разные типы данных даты, но при этом один из них имеет больше частей времени или обладает более высокой точностью, DATEDIFF_BIG присваивает значения 0 недостающим частям другого аргумента.

Границы, задаваемые аргументом datepart

Приведенные ниже инструкции имеют одинаковые значения аргументов startdate и enddate. Указанные даты являются соседними, а временная разница между ними составляет 100 наносекунд (0,0000001 секунды). Разница между аргументами startdate и enddate в каждой инструкции пересекает одну календарную или временную границу аргумента datepart. Каждое выражение возвращает значение 1. Если аргументы startdate и enddate имеют разные значения года, но одинаковые значения календарной недели, DATEDIFF_BIG вернет 0 для части week аргумента datepart.

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

Замечания

Используйте DATEDIFF_BIG в предложениях SELECT <list>, WHERE, HAVING, GROUP BY и ORDER BY.

Функция DATEDIFF_BIG неявно приводит строковые литералы к типу datetime2. Это означает, что DATEDIFF_BIG не поддерживает формат ГЧМ (год, число, месяц) при передаче даты в виде строки. Для использования формата ГЧМ (год, число, месяц) необходимо явно привести строку к типу datetime или smalldatetime.

Указание SET DATEFIRST не влияет на DATEDIFF_BIG. DATEDIFF_BIG всегда считает воскресенье первым днем недели, чтобы обеспечить детерминизм работы функции.

DATEDIFF_BIG может переполняться с точностью до наносекунды, если разница между enddate и startdate представляет собой значение, выходящее за пределы диапазона, допустимого для bigint.

Примеры

Указание столбцов в качестве начальной и конечной даты

В этом примере выражения различного типа используются в качестве аргументов для параметров 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_BIG(day, startDate, endDate) AS 'Duration'  
    FROM dbo.Duration;  
-- Returns: 1  

Определение разницы между startdate и enddate в виде строковых компонентов даты

DECLARE @date1 DATETIME2, @date2 DATETIME2, @result VARCHAR(100)
DECLARE @years BIGINT, @months BIGINT, @days BIGINT, @hours BIGINT, @minutes BIGINT, @seconds BIGINT, @milliseconds BIGINT

SET @date1 = '0001-01-01 00:00:00.00000000'
SET @date2 = '2018-12-12 07:08:01.12345678'

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

Вот результирующий набор.

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

Сопутствующие примеры см. в статье DATEDIFF (Transact-SQL).

Связанные материалы

• CAST и CONVERT (Transact-SQL)
• DATEDIFF (Transact-SQL)