DATETRUNC (Transact-SQL)

Artikel
10/31/2024

Gilt für: SQL Server 2022 (16.x) Azure SQL-Datenbank Azure SQL verwaltete Instanz SQL Analytics-Endpunkt in Microsoft Fabric Warehouse in Microsoft Fabric Warehouse in Microsoft Fabric

Die Funktion DATETRUNC gibt ein Eingabedatum (date) zurück, das an einem angegebenen Datumsteil (datepart) abgeschnitten ist.

Hinweis

DATETRUNC wurde in SQL Server 2022 (16.x) eingeführt.

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 die gleiche Weise abgeschnitten wie `day`
`day`	`dd`, `d`	`day` wird auf die gleiche Weise abgeschnitten wie `dayofyear`
`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. Bei einer US-englischen Umgebung `@@DATEFIRST` ist `7` standardmäßig (Sonntag) festgelegt.
`iso_week`	`isowk`, `isoww`	Abschneiden bis zum ersten Tag einer ISO-Woche. Der erste Tag der Woche im ISO8601-Kalendersystem ist Montag.
`hour`	`hh`
`minute`	`mi, n`
`second`	`ss`, `s`
`millisecond`	`ms`
`microsecond`	`mcs`

Hinweis

Die T-SQL-Datumsparts für Wochentag, Zeitzonenoffset und Nanosekunden werden nicht DATETRUNCunterstü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), das zu einem Datetime2(7)-Wert aufgelöst werden kann.

Rückgabetypen

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). Damit die Zeitskala zum millisecond Datumsteil abgeschnitten werden kann, muss die Bruchzeitskala mindestens 3 sein. Ebenso muss die Bruchzeitskala mindestens 6 sein, um sie auf das microsecond Datumsteil abschneiden zu können. DATETRUNC unterstützt das nanosecond Datumsteil nicht, da kein T-SQL-Datumstyp eine Bruchzeitskala 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);

Hier sehen Sie das Ergebnis.

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

Die folgenden Beispiele veranschaulichen die Verwendung der @@DATEFIRST Einstellung mit dem 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);

Hier sehen Sie das Ergebnis.

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

Hier sehen Sie das Ergebnis.

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

Hier sehen Sie das Ergebnis. Die Eingabedatumszeit und der abgeschnittene Datumsparameter sind identisch.

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

Hier sehen Sie das Ergebnis. Die Eingabedatumszeit und der abgeschnittene Datumsparameter sind identisch.

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

Hier sehen Sie das Ergebnis. Der Eingabe-Smalldatetime-Wert ist identisch mit den abgeschnittenen Werten:

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 einer Datumstime zu einer Millisekunden zu Ergebnissen führen, die sich von den Erwartungen des Benutzers unterscheiden. 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 sehen Sie das Ergebnis. Das abgeschnittene Datum entspricht dem gespeicherten Datum. Dies kann anders sein als das, was Sie basierend auf der DECLARE Anweisung erwarten.

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

Hinweise

Ein Fehler wird ausgelöst, wenn das Abschneiden des Datums versucht, ein Datum vor dem von diesem Datentyp unterstützten Mindestdatum zurückzutracken. Dieser Fehler tritt nur bei Verwendung des week Dateparts auf. Sie kann nicht auftreten, wenn Sie das iso_week Datumsteil verwenden, da alle T-SQL-Datumstypen einen Montag für ihre Mindesttermine 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.

Wenn die DATETRUNC Funktion oder der Eingabedatumsdatentyp nicht unterstützt wird, wird ein DATEPART Fehler ausgelöst. Dieser Fehler kann auftreten, wenn:

Ein von DATETRUNC nicht unterstützter datepart wird verwendet (nämlich, weekday, tzoffset oder nanosecond)

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.

Das Datepart erfordert eine höhere Genauigkeit der Bruchzeitskala, als der Datentyp unterstützt. Weitere Informationen finden Sie unter Genauigkeit der Bruchzeitskala. 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.
```

Freigeben über

DATETRUNC (Transact-SQL)

Syntax

Argumente

datepart

date

Rückgabetypen

Genauigkeit der Zeitbruchteilsskala

Beispiele

A. Verwenden verschiedener datepart-Optionen

B. @@DATEFIRST-Einstellung

C. Datumsliterale

D: Variablen und der date-Parameter

E. Spalten und der date-Parameter

F. Ausdrücke und der date-Parameter

G. Abschneiden eines date auf einen datepart, der seine maximale Genauigkeit darstellt

Beispiel 1

Beispiel 2

Beispiel 3: smalldatetime-Genauigkeit

Beispiel 4: datetime-Genauigkeit

Hinweise

Feedback

Zusätzliche Ressourcen

Freigeben über

DATETRUNC (Transact-SQL)

Syntax

Argumente

datepart

date

Rückgabetypen

Genauigkeit der Zeitbruchteilsskala

Beispiele

A. Verwenden verschiedener datepart-Optionen

B. @@DATEFIRST-Einstellung

C. Datumsliterale

D: Variablen und der date-Parameter

E. Spalten und der date-Parameter

F. Ausdrücke und der date-Parameter

G. Abschneiden eines date auf einen datepart, der seine maximale Genauigkeit darstellt

Beispiel 1

Beispiel 2

Beispiel 3: smalldatetime-Genauigkeit

Beispiel 4: datetime-Genauigkeit

Hinweise

Zugehöriger Inhalt

Feedback

Zusätzliche Ressourcen