DATETRUNC (Transact-SQL)

Gilt für: SQL Server 2022 (16.x) Preview

Ab SQL Server 2022 (16.x) Preview gibt diese Funktion 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:

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

  2. 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.
    
  3. 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