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
). Таким образом, для усечения до 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;
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
. Это происходит только при использовании week
части даты. Это не может произойти при использовании 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
или типом данных входной даты, возникает ошибка DATEPART
. Это может произойти в указанных ниже случаях.
Используется аргумент datepart, не поддерживаемый
DATETRUNC
(а именноweekday
,tzoffset
илиnanosecond
).Аргумент 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.
Для 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.
См. также
Обратная связь
https://aka.ms/ContentUserFeedback.
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделеОтправить и просмотреть отзыв по