DATETRUNC (Transact-SQL)

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

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

Синтаксис

DATETRUNC ( datepart , date )

Аргументы

datepart

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

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). Таким образом, для усечения до millisecond части даты шкала дробного времени должна быть не менее 3. Аналогичным образом, чтобы усечь на microsecond часть даты, шкала дробного времени должна быть не менее 6. DATETRUNCне поддерживает часть даты, так как тип даты T-SQL не поддерживает nanosecond дробный шкалу времени 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 параметра с элементом week datepart:

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;
GO

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);

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

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);

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

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, хранящимся внутри системы.

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

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

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

Замечания

Ошибка возникает, если усечение даты пытается вернуться к дате до минимальной даты, поддерживаемой этим типом данных. Эта ошибка возникает только при использовании week параметра datepart. Это не может произойти при использовании iso_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 функция или тип данных входной даты не поддерживает используемый элемент даты. Эта ошибка может возникать, когда:

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)