DATETRUNC (Transact-SQL)
Gilt für:
SQL Server 2022 (16.x) Azure SQL-DatenbankAzure SQL Managed InstanceSQL Analytics-Endpunkt in Microsoft FabricWarehouse in Microsoft Fabric
Die Funktion DATETRUNC gibt ein Eingabedatum (date) zurück, das an einem angegebenen Datumsteil (datepart) abgeschnitten ist.
Syntax
DATETRUNC ( datepart, date )
Argumente
datepart
Gibt die Genauigkeit für das Abschneiden an. Diese Tabelle enthält alle gültigen datepart-Werte für DATETRUNC, vorausgesetzt, dass es sich auch um einen gültigen Teil des Eingabedatumstyps handelt.
| datepart | Abkürzungen | Hinweise zum Abschneiden |
|---|---|---|
| year | yy, yyyy | |
| quarter | qq, q | |
| month | mm, m | |
| dayofyear | dy, y | dayofyear wird auf dieselbe Weise wie day abgeschnitten. |
| day | dd, d | day wird auf dieselbe Weise wie dayofyear abgeschnitten. |
| week | wk, ww | Auf den ersten Tag der Woche abschneiden. In T-SQL ist der erste Tag der Woche durch die T-SQL-Einstellung @@DATEFIRST definiert. In einer US-englischen Umgebung ist der Wert von @@DATEFIRST standardmäßig 7 (Sonntag). |
| iso_week | isowk, isoww | Auf den ersten Tag einer ISO-Woche abschneiden. Der erste Tag der Woche im ISO8601-Kalendersystem ist Montag. |
| hour | hh | |
| minute | mi, n | |
| second | ss, s | |
| millisecond | ms | |
| microsecond | mcs |
Hinweis
Die „dateparts“ (Datumsteile) von T-SQL weekday, timezoneoffset und nanoseconds (Wochentag, Zeitzonenoffset, Nanosekunden) werden für DATETRUNC nicht unterstützt.
date
Akzeptiert eine/n beliebige/n Ausdruck, Spalte oder benutzerdefinierte Variable, der/die zu einem beliebigen T-SQL-Datums- oder -Uhrzeittyp aufgelöst werden kann. Gültige Typen sind:
- smalldatetime
- datetime
- date
- time
- datetime2
- datetimeoffset
Verwechseln Sie den Parameter date nicht mit dem Datumstyp date.
DATETRUNC akzeptiert auch ein Zeichenfolgenliteral (eines beliebigen Zeichenfolgentyps), der zu einer datetime2(7) aufgelöst werden kann.
Rückgabetyp
Der zurückgegebene Datentyp für DATETRUNC ist dynamisch. DATETRUNC gibt ein abgeschnittenes Datum desselben Datentyps (und ggf. derselben Zeitbruchteilsskala) wie das Eingabedatum zurück. Wenn an DATETRUNC beispielsweise ein datetimeoffset(3)-Eingabedatum übergeben würde, gäbe es einen datetimeoffset(3) zurück. Wenn ein Zeichenfolgenliteral an die Funktion übergeben würde, das zu einer datetime2(7) aufgelöst werden könnte, würde DATETRUNC eine datetime2(7) zurückgeben.
Genauigkeit der Zeitbruchteilsskala
Millisekunden verfügen über eine Zeitbruchteilsskala von 3 (.123), Mikrosekunden verfügen über eine Zeitbruchteilsskala von 6 (.123456), und Nanosekunden haben eine Zeitbruchteilsskala von 9 (.123456789). Die Datentypen time, datetime2 und datetimeoffset gestatten eine maximale Zeitbruchteilsskala von 7 (.1234567). Um daher auf den datepartmillisecond abzuschneiden, muss die Zeitbruchteilsskala mindestens 3 betragen. Ähnlich muss, um auf den datepartmicrosecond abzuschneiden, die Zeitbruchteilsskala mindestens 6 betragen. DATETRUNC unterstützt den datepartnanosecond nicht, da kein T-SQL-Datumstyp eine Zeitbruchteilsskala von 9 unterstützt.
Beispiele
A. Verwenden verschiedener datepart-Optionen
Die folgenden Beispiele veranschaulichen die Verwendung verschiedener datepart-Optionen:
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);
Das Ergebnis lautet wie folgt:
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-Einstellung
In den folgenden Beispielen wird die Verwendung der @@DATEFIRST-Einstellung mit dem datepartweek veranschaulicht:
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);
Das Ergebnis lautet wie folgt:
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. Datumsliterale
Die folgenden Beispiele veranschaulichen die Verwendung von date-Parameterliteralen:
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);
Hier sehen Sie das Resultset (alle Ergebnisse sind vom Typ 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. Variablen und der date-Parameter
Im folgenden Beispiel wird die Verwendung des date-Parameters veranschaulicht:
DECLARE @d datetime2 = '1998-12-11 02:03:04.1234567';
SELECT DATETRUNC(day, @d);
Das Ergebnis lautet wie folgt:
1998-12-11 00:00:00.0000000
E. Spalten und der date-Parameter
Die TransactionDate-Spalte aus der Sales.CustomerTransactions-Tabelle dient als Beispiel für das column-Argument des date-Parameters:
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. Ausdrücke und der date-Parameter
Der date-Parameter akzeptiert einen beliebigen Ausdruck, der in einen T-SQL-Datumstyp aufgelöst werden kann, oder ein beliebiges Zeichenfolgenliteral, das in eine datetime2(7) aufgelöst werden kann. Die TransactionDate-Spalte aus der Sales.CustomerTransactions-Tabelle dient als künstliches Argument, um die Verwendung eines Ausdrucks für den date-Parameter zu verdeutlichen:
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. Abschneiden eines date auf einen datepart, der seine maximale Genauigkeit darstellt
Wenn der datepart die gleiche maximale Einheitengenauigkeit wie der Eingabedatumstyp aufweist, hätte das Abschneiden des Eingabedatums auf diesen datepart keine Auswirkung.
Beispiel 1
DECLARE @d datetime = '2015-04-29 05:06:07.123';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);
Nachfolgend ist das Resultset angegeben, das veranschaulicht, dass die Eingabe-datetime und der abgeschnittene date-Parameter identisch sind:
Input 2015-04-29 05:06:07.123
Truncated 2015-04-29 05:06:07.123
Beispiel 2
DECLARE @d date = '2050-04-04';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(day, @d);
Nachfolgend ist das Resultset angegeben, das veranschaulicht, dass die Eingabe-datetime und der abgeschnittene date-Parameter identisch sind:
Input 2050-04-04
Truncated 2050-04-04
Beispiel 3: smalldatetime-Genauigkeit
smalldatetime ist nur bis zur nächsten Minute genau, auch wenn es ein Feld für Sekunden hat. Daher hätte das Abschneiden auf die nächste Minute oder die nächste Sekunde keine Auswirkung.
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);
Das Resultset veranschaulicht, dass der Eingabewert smalldatetime mit den beiden abgeschnittenen Werten identisch ist:
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
Beispiel 4: datetime-Genauigkeit
datetime ist nur bis zu 3,33 Millisekunden genau. Daher kann das Abschneiden eines datetime-Werts auf eine Millisekunde Ergebnisse liefern, die von den Erwartungen des Benutzers abweichen. Dieser abgeschnittene Wert ist jedoch identisch mit dem intern gespeicherten datetime-Wert.
DECLARE @d datetime = '2020-02-02 02:02:02.002';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);
Hier ist das Resultset angegeben, das veranschaulicht, dass das abgeschnittene date mit dem gespeicherten date identisch ist. Dies kann sich von dem unterscheiden, was Sie aufgrund der DECLARE-Anweisung erwarten.
Input 2020-02-02 02:02:02.003
Truncated 2020-02-02 02:02:02.003
Bemerkungen
Ein DATE TOO SMALL-Fehler wird ausgelöst, wenn durch das Abschneiden von date versucht wird, zu einem Datum zurückzukehren, das vor dem von diesem Datentyp unterstützten Mindestdatum liegt. Dies tritt nur auf, wenn Sie den datepartweek verwenden. Es kann nicht auftreten, wenn Sie den datepartiso_week verwenden, da alle T-SQL-Datumstypen übereinstimmend einen Montag als Mindestdatum verwenden. Hier sehen Sie ein Beispiel mit der entsprechenden Ergebnisfehlermeldung:
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.
Ein DATEPART-Fehler wird ausgelöst, wenn der verwendete datepart nicht von der DATETRUNC-Funktion oder dem Datentyp des Eingabedatums unterstützt wird. Dies kann auftreten, wenn Folgendes zutrifft:
Ein von
DATETRUNCnicht unterstützter datepart wird verwendet (nämlich,weekday,tzoffsetodernanosecond)Ein time-bezogener datepart wird mit dem Datentyp date oder ein date-bezogener datepart mit dem Datentyp time verwendet. Hier sehen Sie ein Beispiel mit der entsprechenden Ergebnisfehlermeldung:
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.Der datepart erfordert eine höhere Genauigkeit der Zeitbruchteilsskala als vom Datentyp unterstützt wird (siehe den Abschnitt Genauigkeit der Zeitbruchteilsskala). Hier sehen Sie ein Beispiel mit der entsprechenden Ergebnisfehlermeldung:
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.
Siehe auch
Feedback
Bald verfügbar: Im Laufe des Jahres 2024 werden wir GitHub-Issues stufenweise als Feedbackmechanismus für Inhalte abbauen und durch ein neues Feedbacksystem ersetzen. Weitere Informationen finden Sie unter https://aka.ms/ContentUserFeedback.
Feedback senden und anzeigen für