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.
Nota
DATETRUNC
è stato introdotto in SQL Server 2022 (16.x).
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.
datepart | Abbreviations | Note sul troncamento |
---|---|---|
year |
yy , yyyy |
|
quarter |
qq , q |
|
month |
mm , m |
|
dayofyear |
dy , y |
dayofyear viene troncato allo stesso modo di day |
day |
dd , d |
day viene troncato allo stesso modo di dayofyear |
week |
wk , ww |
La data viene troncata in base al primo giorno della settimana. In T-SQL il primo giorno della settimana viene definito dall'impostazione T-SQL @@DATEFIRST . Per un ambiente inglese degli Stati Uniti, @@DATEFIRST per impostazione predefinita 7 è (domenica). |
iso_week |
isowk , isoww |
Tronca al primo giorno di una settimana ISO. Nel sistema di calendario ISO8601, il primo giorno della settimana è lunedì. |
hour |
hh |
|
minute |
mi, n |
|
second |
ss , s |
|
millisecond |
ms |
|
microsecond |
mcs |
Nota
Le datepart T-SQL del giorno della settimana, del fuso orario e del nanosecondo non sono supportate per DATETRUNC
.
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. DATETRUNC
non 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);
Questo è il set di risultati.
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);
Questo è il set di risultati.
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);
Questo è il set di risultati. 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);
Questo è il set di risultati.
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);
Questo è il set di risultati. 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);
Questo è il set di risultati. 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);
Questo è il set di risultati. 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);
Questo è il set di risultati. 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:
Viene usato un elemento datepart non supportato da
DATETRUNC
(vale a essere,weekday
,tzoffset
onanosecond
)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.
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.