DATEDIFF_BIG (Transact-SQL)
Применимо к:
SQL Server 2016 (13.x) и более поздних версий Azure SQL Database Управляемый экземпляр SQL Azure
Эта функция возвращает количество пересеченных границ (целое число со знаком), указанных в аргументе 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, yyyy |
| quarter | qq, q |
| month | mm, m |
| dayofyear | dy, y |
| day | dd, d |
| week | wk, ww |
| hour | hh |
| minute | mi, n |
| 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');
Remarks
Используйте 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)
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по