Da: Jim Evans | Aggiornato: 2020-07-16 | Commenti (1) | Correlati: More > Date
Problema
Spesso, abbiamo bisogno di calcolare la differenza tra due date e restituire i risultati in una parte o incremento di data desiderato come giorni, ore, minuti. Fortunatamente, SQLServer fornisce una funzione per questo.
In questo articolo mostrerò le funzioni SQL DATEDIFF e DATEDIFF_BIG e condividerò diversi esempi di come usarle. Vi mostrerò anche le limitazioni e come aggirarle. Imparerete anche come calcolare quanti anni ha la città di St.Augustine in nanosecondi.
Soluzione
Esploreremo le funzioni DATEDIFF e DATEDIFF_BIG, mostreremo come vengono usate e forniremo diversi esempi.
Cos’è la funzione SQL Server DATEDIFF
DATEDIFF() è una funzione SQL Server di base che può essere usata per fare calcoli di date. In particolare, ottiene la differenza tra 2 date con i risultati restituiti in unità di data specificate come anni, mesi, giorni, minuti, secondi come un valore int (intero).
Sintassi:
DATEDIFF( DatePart, StartDate, EndDate )
Cos’è la funzione SQL Server DATEDIFF_BIG
DATEDIFF_BIG() è una funzione SQL introdotta in SQL Server 2016. Può essere usata anche per fare la matematica delle date. In particolare, ottiene la differenza tra 2 date con i risultati restituiti in unità di data specificate come anni, mesi, giorni, minuti, secondi come valore bigint.
Sintassi:
DATEDIFF_BIG( DatePart, StartDate, EndDate )
Come usare DATEDIFF e DATEDIFF_BIG
L’uso di DATEDIFF e DATEDIFF_BIG è lo stesso: DATEDIFF(datepart of returnvalues, Start Date, End Date).
Esempi:
SELECT DATEDIFF( MILLISECOND, '07-04-2020', '07-05-2020') --> = 86400000 SELECT DATEDIFF_BIG( NANOSECOND, '07-04-2020', '07-05-2020') --> = 86400000000000
Primo parametro: è un valido argomento datapart che è uno dei seguenti da nanosecondo a anno:
| Nome della parte data | Abbreviazione | |
|---|---|---|
| anno | yy, yyyy | |
| trimestre | qq, q | |
| mese | mm, m | |
| giorno dell’anno | dy, y | |
| giorno | dd, d | |
| settimana | wk, ww | |
| ora | hh | |
| minuto | mi, n | |
| secondo | ss, s | |
| millisecondo | ms | |
| microsecondo | mcs | |
| nanosecondo | ns |
Secondo parametro: è la data di inizio. Una data valida, datetime, datetimeoffset, datetime2, smalldatetime, o variabile di tipo tempo o una stringa risolta in un datatype datetime.
Terzo Parametro: è la End Date. Una variabile di tipo validdate, datetime, datetimeoffset, datetime2, smalldatetime, o time o una stringa che si risolve in un datatype datetime.
*Questa informazione può essere trovata su Microsoft Docs!
Esempi di SQL Server DATEDIFF
Di seguito ci sono esempi di base usando gli argomenti più comuni delle datapart.
| Esempio | Codice SQL Server DATEDIFF | Output |
|---|---|---|
| Quanti minuti in un giorno? | SELECT DATEDIFF(MINUTE, ’07-04-2020′, ’07-05-2020′) | 1440 minuti |
| Quante ore in un giorno? | SELECT DATEDIFF(HOUR, ’01-01-2020′, ’01-02-2020′) | 24 ore |
| Quanti giorni in un anno? | SELECT DATEDIFF(DAY, ’01-01-2020′, ’12-31-2020′) | 365 Giorni |
| Quanti mesi in un anno? | SELECT DATEDIFF(MONTH, ’01-01-2019′, ’01-01-2020′) | 12 Mesi |
| Quanti anni dal 2000 al 2020? | SELECT DATEDIFF(YEAR, ’01-01-2000′, ’01-01-2020′) | 20 anni |
Quando usare la funzione DATEDIFF_BIG di SQL Server
Utilizzare la funzione DATEDIFF_BIG quando i risultati superano la gamma di un valore intero che è compreso tra (-2.147.483.648 a +2.147.483.647).
BIGINT ha un intervallo di (-9.223.372.036.854.775.808 a 9.223.372.036.854.775.807). Sembra che sarebbe difficile superare questo intervallo! Ma presenteremo un esempio che supera questo intervallo quando si usa DATEDIFF_BIG.
QL Server DATEDIFF_BIG invece di DATEDIFF
Come superare il valore di ritorno di DATEDIFF int? Microsoft Docs fornisce 2 esempi di come superare il valore di ritorno dell’intero DATEDIFF che io dimostro qui sotto e mostro come funziona DATEDIFF_BIG per aggirare la limitazione.
--1. For millisecond, max difference between startdate and enddate is 24 days, 20 hours, 31 minutes and 23.647 seconds. -- the following will exceed this range.SELECT DATEDIFF(MILLISECOND, '01-01-2020', '02-01-2020')
Questo risulta nel seguente errore.
The datediff function resulted in anoverflow. Il numero di dateparts che separano due istanze di data/ora è troppo grande. Prova ad usare datediff con una parte di data meno precisa.
Ecco un altro esempio.
--2. For second, the maximum difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds. -- the following will exceed this range.SELECT DATEDIFF(SECOND, '01-01-1950', '02-01-2020')
Questo risulta nel seguente errore.
La funzione datediff ha prodotto un overflow. Il numero di dateparts che separano due istanze di data/ora è troppo grande. Prova ad usare datediff con una datapart meno precisa.
Utilizzare invece DATEDIFF_BIG per evitare gli errori di cui sopra.
--3. How to get around the integer limit using DATEDIFF_BIG functionSELECT DATEDIFF_BIG(MILLISECOND, '01-01-2020', '02-01-2020') --> = 2678400000SELECT DATEDIFF_BIG(SECOND, '01-01-1950', '02-01-2020') –-> = 2211667200
Quando DATEDIFF_BIG non è abbastanza grande
Secondo Microsoft Docs, DATEDIFF_BIG può traboccare solo se si usa la precisione al nanosecondo dove la differenza tra enddate e startdate è più di 292 anni, 3 mesi, 10 giorni, 23 ore, 47 minuti e 16. 8547758 secondi.8547758 secondi.
Questo potrebbe essere un problema se vuoi sapere quanti nanosecondi sono passati da quando la più antica città americana, St. Augustine in Florida è stata fondata!
--1. How many nanoseconds since St. Augustine was founded on Sept. 8, 1565?SELECT DATEDIFF_BIG(NANOSECOND, '09-08-1565', GETDATE())
Bummer! Abbiamo un errore.
La funzione datediff_big ha prodotto un overflow. Il numero di datepart che separa due istanze di data/ora è troppo grande. Prova a usare datediff_big con una datepart meno precisa.
Proviamolo in più passi:
--1. First get the number of Years since St. Augustine was foundedSELECT DATEDIFF_BIG(YEAR, '09-08-1565', GETDATE()) --> = 455 --2. Next, get the number of nanoseconds in a yearSELECT DATEDIFF_BIG(NANOSECOND, '01-01-2020 00:00:00.0000000', '12-31-2020 23:59:59.9999999') --> = 31622399999999900--3. Last multiply the results together and cast to decimal(38,0)-- 455 x 31622399999999900 = 14388191999999954500SELECT CAST(455 * 31622399999999900 as DECIMAL(38,0)) --> = 14388191999999954500
Walla! Ora sappiamo quanti nanosecondi dalla fondazione di St. Augustine.
Wrap Up
Spero che vi sia piaciuto questo esercizio su DATEDIFF e DATEDIFF_BIG. DATEDIFF è una funzione SQL comunemente usata che è stata in giro per molti anni. DATEDIFF_BIG è una funzione relativamente nuova che è stata introdotta in SQL Server 2016. Anche se ho fornito esempi di overflow da queste funzioni, è raro che si incontrino questi scenari. Queste sono funzioni pratiche che dovrebbero essere aggiunte alla vostra cassetta degli attrezzi SQL!
Passi successivi
- Vai più in profondità con -altri articoli su Datediff
- Leggi su -work arounds per il calcolo dell’età
- Leggi su altre interessanti funzioni SQL -LAG e LEAD
- Trova altri articoli su Datetime -MS SQL Tips Cerca su DateDiff
Ultimo aggiornamento: 2020-07-16
Informazioni sull’autore
Jim Evans è un IT Manager attualmente in Harsco che ha gestito DBA, sviluppatori di applicazioni e BI e team di gestione dei dati per oltre 20 anni.Vedi tutti i miei consigli
- Altri consigli per sviluppatori di database…