DATETRUNC (Transact-SQL)

Si applica a: SQL Server 2022 (16.x) database SQL di Azure Istanza gestita di SQL di Azure endpoint di analisi SQL in Microsoft Fabric Warehouse in Microsoft Fabric

La DATETRUNC funzione restituisce una data di input troncata a una datepart specificata.

Sintassi

DATETRUNC ( datepart , date )

Argomenti

datepart

Specifica la precisione per il troncamento. Questa tabella elenca tutti i valori datepart validi per DATETRUNC, dato che è anche una parte valida del tipo di data di input.

date

Accetta qualsiasi espressione, colonna o variabile definita dall'utente che può essere risolta in qualsiasi tipo di data o ora T-SQL valido. I tipi validi sono:

• smalldatetime
• datetime
• date
• time
• datetime2
• datetimeoffset

Non confondere il parametro date con il tipo di dati date .

DATETRUNC accetta anche un valore letterale stringa (di qualsiasi tipo stringa) che può essere risolto in un valore datetime2(7).

Tipi restituiti

Il tipo di dati restituito per DATETRUNC è dinamico. DATETRUNC restituisce una data troncata dello stesso tipo di dati (e, se applicabile, la stessa scala temporale frazionaria) della data di input. Ad esempio, se DATETRUNC è stata assegnata una data di input datetimeoffset(3), restituisce un datetimeoffset(3). Se è stato assegnato un valore letterale stringa che potrebbe risolversi in un valore datetime2(7),restituirebbe DATETRUNC un valore datetime2(7).

Precisione della scala temporale frazionaria

La scala temporale frazionaria dei millisecondi è in base 3 (), mentre quella dei microsecondi è in base 6 (.123) e quella dei nanosecondi è in base 9 (.123456.123456789). La scala temporale frazionaria massima dei tipi di dati time, datetime2 e datetimeoffset è in base 7 (.1234567). Pertanto, per troncare alla millisecond datepart, la scala temporale frazionaria deve essere almeno 3. Analogamente, per troncare alla microsecond datepart, la scala temporale frazionaria deve essere almeno 6. DATETRUNCnon supporta datepart nanosecond perché nessun tipo di data T-SQL supporta una scala temporale frazionaria pari a 9.

Esempi

R. Usare opzioni datepart diverse

Gli esempi seguenti illustrano l'uso di varie opzioni 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);

Il set di risultati è il seguente.

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. Impostazione @@DATEFIRST

Gli esempi seguenti illustrano l'uso dell'impostazione @@DATEFIRST con datepartweek:

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

Il set di risultati è il seguente.

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. Valori letterali data

Gli esempi seguenti illustrano l'uso dei valori letterali dei parametri di data :

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

Il set di risultati è il seguente. Tutti i risultati sono di tipo 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. Variabili e il parametro date

Nell'esempio seguente viene illustrato l'uso del parametro date :

DECLARE @d datetime2 = '1998-12-11 02:03:04.1234567';
SELECT DATETRUNC(day, @d);

Il set di risultati è il seguente.

1998-12-11 00:00:00.0000000

E. Colonne e il parametro date

La TransactionDate colonna della Sales.CustomerTransactions tabella funge da argomento di colonna di esempio per il parametro 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. Espressioni e il parametro date

Il parametro date accetta qualsiasi espressione in grado di risolvere in un tipo di data T-SQL o in qualsiasi valore letterale stringa che può essere risolto in un valore datetime2(7). La TransactionDate colonna della Sales.CustomerTransactions tabella funge da argomento artificiale per esemplificare l'uso di un'espressione per il parametro 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. Tronca una data a un datepart che rappresenta la precisione massima

Se datepart ha la stessa precisione massima dell'unità del tipo di data di input, il troncamento della data di input a questo datepart non avrà alcun effetto.

Esempio 1

DECLARE @d datetime = '2015-04-29 05:06:07.123';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(millisecond, @d);

Il set di risultati è il seguente. Il valore datetime di input e il parametro date troncato sono gli stessi.

Input     2015-04-29 05:06:07.123
Truncated 2015-04-29 05:06:07.123

Esempio 2

DECLARE @d date = '2050-04-04';
SELECT 'Input', @d;
SELECT 'Truncated', DATETRUNC(day, @d);

Il set di risultati è il seguente. Il valore datetime di input e il parametro date troncato sono gli stessi.

Input     2050-04-04
Truncated 2050-04-04

Esempio 3: precisione smalldatetime

smalldatetime è preciso solo fino al minuto più vicino, anche se include un campo per i secondi. Troncare la data al minuto o al secondo più vicino non avrebbe quindi alcun effetto.

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

Il set di risultati è il seguente. Il valore smalldatetime di input corrisponde a entrambi i valori troncati:

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

Esempio 4: precisione datetime

datetime è preciso solo fino a 3,33 millisecondi. Di conseguenza, il troncamento di un valore datetime a un millisecondo potrebbe produrre risultati diversi da quelli previsti dall'utente. Questo valore troncato corrisponde tuttavia al valore datetime archiviato internamente.

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

Il set di risultati è il seguente. La data troncata corrisponde alla data archiviata. Questo potrebbe essere diverso da quello previsto in base all'istruzione DECLARE .

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

Osservazioni:

Viene generato un errore se il troncamento della data tenta di eseguire il backtracking a una data prima della data minima supportata da tale tipo di dati. Questo errore si verifica solo quando si usa datepart week . Non può verificarsi quando si usa datepart iso_week , poiché tutti i tipi di data T-SQL usano in modo casuale un lunedì per le date minime. Di seguito è riportato un esempio con il messaggio di errore corrispondente:

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.

Se DATEPART la DATETRUNC funzione o il tipo di dati data di input non supportano il valore datepart usato, viene generato un errore. Questo errore può verificarsi quando:

1. Viene usato un elemento datepart non supportato da DATETRUNC (vale a essere, weekday, tzoffseto nanosecond)

2. Un datepart correlato all'ora viene utilizzato con il tipo di dati date o un datepart correlato alla data viene utilizzato con il tipo di dati time. Di seguito è riportato un esempio con il messaggio di errore corrispondente:

   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 richiede una precisione di scala temporale frazionaria superiore rispetto al tipo di dati supportato. Per altre informazioni, vedere Precisione della scala temporale frazionaria. Di seguito è riportato un esempio con il messaggio di errore corrispondente:

   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.
   

Contenuto correlato

• @@DATEFIRST (Transact-SQL)
• DATEPART (Transact-SQL)
• Funzioni e tipi di data e ora (Transact-SQL)