DATETRUNC (Transact-SQL)

Область применения: конечная точка аналитики SQL Server 2022 (16.x) База данных SQL Azure Управляемый экземпляр SQL Azure SQL в хранилище Microsoft Fabricв Microsoft Fabric

Функция DATETRUNC возвращает входную дату , усеченную до указанной части даты.

Синтаксис

DATETRUNC ( datepart, date )

Аргументы

datepart

Указывает точность усечения. В этой таблице перечислены все допустимые значения datepart для DATETRUNC, учитывая, что это также является допустимой частью типа входной даты.

datepart	Abbreviations	Примечания об усечении
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y	dayofyear усекается так же, как day
day	dd, d	day усекается так же, как dayofyear
week	wk, ww	Усечение до первого дня недели. В T-SQL первый день недели определяется параметром T-SQL @@DATEFIRST. Для региональных настроек "Английский (США)" значением @@DATEFIRST по умолчанию является 7 (воскресенье).
iso_week	isowk, isoww	Усечение до первого дня недели ISO. Первый день недели в календарной системе ISO8601 — понедельник.
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs

Примечание.

Для DATETRUNC не поддерживаются аргументы части даты T-SQL weekday, timezoneoffset и nanosecond.

date

Принимает любое выражение, столбец или определяемую пользователем переменную, которая может разрешаться в любой допустимый тип даты или времени T-SQL. Допустимые типы:

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

Не путайте параметр date с типом данных date.

DATETRUNC также принимает строковый литерал (любого типа строки), который может разрешаться в datetime2(7).

Возвращаемый тип

Возвращаемый тип данных для DATETRUNC является динамическим. DATETRUNC возвращает усеченную дату того же типа данных (и ту же дробную шкалу времени, если применимо), что и входная дата. Например, если функции DATETRUNC была предоставлена входная дата datetimeoffset(3), она вернет datetimeoffset(3). Если ей был предоставлен строковый литерал, который может разрешиться в datetime2(7), функция DATETRUNC вернет datetime2(7).

Точность дробной шкалы времени

Миллисекунды имеют дробную шкалу времени 3 (.123), микросекунды имеют дробную шкалу времени 6 (.123456), а наносекунды имеют дробную шкалу времени 9 (.123456789). Типы данных time, datetime2 и datetimeoffset допускают максимальную дробную шкалу времени 7 (.1234567). Таким образом, для усечения datepartmillisecond дробная шкала времени должна быть не менее 3. Аналогичным образом, для усечения datepartmicrosecond дробная шкала времени должна быть не менее 6. DATETRUNC не поддерживает аргумент datepartnanosecond, так как тип даты T-SQL не поддерживает дробную шкалу времени 9.

Примеры

А. Использование различных параметров datepart

В следующих примерах показано использование различных параметров datepart:

DECLARE @d datetime2 = '2021-12-08 11:30:15.1234567';
SELECT 'Year', DATETRUNC(year, @d);
SELECT 'Quarter', DATETRUNC(quarter, @d);
SELECT 'Month', DATETRUNC(month, @d);
SELECT 'Week', DATETRUNC(week, @d); -- Using the default DATEFIRST setting value of 7 (U.S. English)
SELECT 'Iso_week', DATETRUNC(iso_week, @d);
SELECT 'DayOfYear', DATETRUNC(dayofyear, @d);
SELECT 'Day', DATETRUNC(day, @d);
SELECT 'Hour', DATETRUNC(hour, @d);
SELECT 'Minute', DATETRUNC(minute, @d);
SELECT 'Second', DATETRUNC(second, @d);
SELECT 'Millisecond', DATETRUNC(millisecond, @d);
SELECT 'Microsecond', DATETRUNC(microsecond, @d);

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

Year        2021-01-01 00:00:00.0000000
Quarter     2021-10-01 00:00:00.0000000
Month       2021-12-01 00:00:00.0000000
Week        2021-12-05 00:00:00.0000000
Iso_week    2021-12-06 00:00:00.0000000
DayOfYear   2021-12-08 00:00:00.0000000
Day         2021-12-08 00:00:00.0000000
Hour        2021-12-08 11:00:00.0000000
Minute      2021-12-08 11:30:00.0000000
Second      2021-12-08 11:30:15.0000000
Millisecond 2021-12-08 11:30:15.1230000
Microsecond 2021-12-08 11:30:15.1234560

B. Параметр @@DATEFIRST

В следующих примерах показано использование параметра @@DATEFIRST с datepartweek:

DECLARE @d datetime2 = '2021-11-11 11:11:11.1234567';

SELECT 'Week-7', DATETRUNC(week, @d); -- Uses the default DATEFIRST setting value of 7 (U.S. English)

SET DATEFIRST 6;
SELECT 'Week-6', DATETRUNC(week, @d);

SET DATEFIRST 3;
SELECT 'Week-3', DATETRUNC(week, @d);

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

Week-7  2021-11-07 00:00:00.0000000
Week-6  2021-11-06 00:00:00.0000000
Week-3  2021-11-10 00:00:00.0000000

C. Литералы даты

В следующих примерах показано использование литералов параметра date:

SELECT DATETRUNC(month, '1998-03-04');

SELECT DATETRUNC(millisecond, '1998-03-04 10:10:05.1234567');

DECLARE @d1 char(200) = '1998-03-04';
SELECT DATETRUNC(millisecond, @d1);

DECLARE @d2 nvarchar(max) = '1998-03-04 10:10:05';
SELECT DATETRUNC(minute, @d2);

Ниже приведен результирующий набор (все результаты имеют тип datetime2(7)):

1998-03-01 00:00:00.0000000
1998-03-04 10:10:05.1230000
1998-03-04 00:00:00.0000000
1998-03-04 10:10:00.0000000

D. Переменные и параметр date

В следующем примере показано, как используется параметр date:

DECLARE @d datetime2 = '1998-12-11 02:03:04.1234567';
SELECT DATETRUNC(day, @d);

Ниже приведен результат:

1998-12-11 00:00:00.0000000

Д. Столбцы и параметр date

Столбец TransactionDate из таблицы Sales.CustomerTransactions служит примером аргумента column для параметра date:

USE WideWorldImporters;

SELECT CustomerTransactionID,
    DATETRUNC(month, TransactionDate) AS MonthTransactionOccurred,
    InvoiceID,
    CustomerID,
    TransactionAmount,
    SUM(TransactionAmount) OVER (
        PARTITION BY CustomerID ORDER BY TransactionDate,
            CustomerTransactionID ROWS UNBOUNDED PRECEDING
        ) AS RunningTotal,
    TransactionDate AS ActualTransactionDate
FROM [WideWorldImporters].[Sales].[CustomerTransactions]
WHERE InvoiceID IS NOT NULL
    AND DATETRUNC(month, TransactionDate) >= '2015-12-01';

F. Выражения и параметр date

Аргумент date принимает любое выражение, которое может разрешаться в тип даты T-SQL, или любой строковый литерал, который может разрешаться в datetime2(7). Столбец TransactionDate из таблицы Sales.CustomerTransactions служит искусственным аргументом для примера использования expression для параметра date:

SELECT DATETRUNC(m, SYSDATETIME());

SELECT DATETRUNC(yyyy, CONVERT(date, '2021-12-1'));

USE WideWorldImporters;
GO
SELECT DATETRUNC(month, DATEADD(month, 4, TransactionDate))
FROM Sales.CustomerTransactions;
GO

G. Усечение date до части даты (datepart), представляющей максимальную точность

Если часть даты (datepart) имеет ту же максимальную точность единицы измерения, что и тип входной даты, усечение входной даты до этой datepart не будет иметь эффекта.

Пример 1

DECLARE @d datetime = '2015-04-29 05:06:07.123';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

Ниже приведен результирующий набор, который демонстрирует, что входной параметр datetime и усеченный параметр date совпадают:

Input     2015-04-29 05:06:07.123
Truncated 2015-04-29 05:06:07.123

Пример 2

DECLARE @d date = '2050-04-04';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(day, @d);

Ниже приведен результирующий набор, который демонстрирует, что входной параметр datetime и усеченный параметр date совпадают:

Input     2050-04-04
Truncated 2050-04-04

Пример 3. Точность smalldatetime

smalldatetime является точным только до ближайшей минуты, несмотря на наличие поля для секунд. Таким образом, усечение его до ближайшей минуты или ближайшей секунды не будет иметь никакого эффекта.

DECLARE @d smalldatetime = '2009-09-11 12:42:12'
SELECT 'Input', @d;
SELECT 'Truncated to minute', DATETRUNC(minute, @d)
SELECT 'Truncated to second', DATETRUNC(second, @d);

Результирующий набор иллюстрирует, что входное значение smalldatetime совпадает с усеченными значениями:

Input                2009-09-11 12:42:00
Truncated to minute  2009-09-11 12:42:00
Truncated to second  2009-09-11 12:42:00

Пример 4. Точность datetime

datetime является точным только до 3,33 миллисекунды. Таким образом, усечение datetime до миллисекунд может привести к результатам, отличным от ожидаемого пользователем. Однако это усеченное значение совпадает со значением datetime, хранящимся внутри системы.

DECLARE @d datetime = '2020-02-02 02:02:02.002';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

Ниже приведен результирующий набор, иллюстрирующий, что усеченное значение date совпадает с сохраненным значением date. Это может отличаться от ожидаемого результата в зависимости от инструкции DECLARE.

Input     2020-02-02 02:02:02.003
Truncated 2020-02-02 02:02:02.003

Замечания

Если усечение date пытается вернуться к дате, предшествующей минимальной дате, поддерживаемой этим типом даты, возникает ошибка DATE TOO SMALL. Это происходит только при использовании datepartweek. Это не может произойти при использовании datepartiso_week, так как все типы дат T-SQL случайно используют понедельник для минимальной даты. Ниже приведен пример с соответствующим сообщением об ошибке для результата:

DECLARE @d date= '0001-01-01 00:00:00';
SELECT DATETRUNC(week, @d);

Msg 9837, Level 16, State 3, Line 84
An invalid date value was encountered: The date value is less than the minimum date value allowed for the data type.

Если используемый аргумент datepart не поддерживается функцией DATETRUNC или типом данных входной даты, возникает ошибка DATEPART. Это может произойти в указанных ниже случаях.

1. Используется аргумент datepart, не поддерживаемый DATETRUNC (а именно weekday, tzoffset или nanosecond).

2. Аргумент datepart, связанный с time, используется с типом данныхdate, либо datepart, связанный с date, используется с типом данных time. Ниже приведен пример с соответствующим сообщением об ошибке для результата:

   DECLARE @d time = '12:12:12.1234567';
   SELECT DATETRUNC(year, @d);
   

   Msg 9810, Level 16, State 10, Line 78
   The datepart year is not supported by date function datetrunc for data type time.
   

3. Для datepart требуется более высокая точность дробной шкалы времени, чем поддерживаемая типом данных (см. раздел Точность дробной шкалы времени). Ниже приведен пример с соответствующим сообщением об ошибке для результата:

   DECLARE @d datetime2(3) = '2021-12-12 12:12:12.12345';
   SELECT DATETRUNC(microsecond, @d);
   

   Msg 9810, Level 16, State 11, Line 81
   The datepart microsecond is not supported by date function datetrunc for data type datetime2.
   

См. также

• @@DATEFIRST (Transact-SQL)
• DATEPART (Transact-SQL)
• Типы данных и функции даты и времени (Transact-SQL)