In questo articolo vedremo come scrivere correttamente una query sul database SQL Server che effettui un filtro su un campo datetime. Occorre prestare molta attenzione perché senza le giuste conoscenze potremmo incappare in risultati errati.
Sintassi di base per filtrare una colonna datetime
Per prima cosa vediamo con quale formato scrivere il valore con cui filtrare una colonna datetime. Il consiglio che mi sento di dare vivamente è quello di utilizzare esclusivamente un formato language neutral che è indipendente da ogni settaggio relativo alla lingua dell’utente che fa login nel database.
Tale formato è YYYYMMDD hh:mi:ss.mmm, occorre cioè riportare in ordine
- anno, mese e giorno
- lo spazio
- ore minuti e secondi intervallati dal carattere due punti “:”
- il carattere punto “.” seguito da tre decimali (opzionale)
Possiamo scrivere ad esempio
SELECT *
FROM MovimentiCarta
WHERE DataMovimento <= '20200101 12:25:23.000';
È fondamentale sottolineare che i decimi di secondo sono arrotondati a .000, .003 o .007. Nel prossimo paragrafo vedremo come la mancata conoscenza di questa informazione può provocare dei gravi errori.
Importanza di usare una sintassi language neutral
Attenzione: per il tipo datetime il formato
YYYY-MM-DD hh:mi:ss.mmm
con il trattino che separa l’anno dal mese e dal giorno non è language neutral. Ad esempio, se il linguaggio della sessione è l’italiano, a meno di impostare esplicitamente il DATEFORMAT (https://learn.microsoft.com/it-it/sql/t-sql/statements/set-dateformat-transact-sql?view=sql-server-ver16), la funzione CAST invertirà giorni e mesi. Ad esempio per questo codice:
SELECT
@@LANGUAGE AS Lingua,
CAST('2020-03-01 15:12:33.112' AS DATETIME) AS Data;
l’output sarà:
Lingua Data
Italiano 2020-01-03 15:12:33.113
Ciò accade anche se riportiamo soltanto anno, mese e data come in questo esempio
SELECT
@@LANGUAGE AS Lingua,
CAST('2020-03-01' AS DATETIME) AS Data;
e anche per cast impliciti:
CREATE TABLE #test (
Lingua VARCHAR(50),
Data DATETIME);
INSERT INTO #test(Lingua, Data)
SELECT @@LANGUAGE AS Lingua,
'2020-03-01';
SELECT *
FROM #test;
In tutti e due i casi infatti, il risultato sarà
Lingua Data
Italiano 2020-01-03 00:00:00.000
Vi ricordo che la lingua di una sessione non è un’impostazione dell’installazione di SQL Server, ma può variare da login a login. I risultati delle query precedenti sarebbero diversi se ad esempio la lingua fosse us_english. Vi consiglio pertanto di utilizzare su SQL Server la forma language neutral senza trattino. Per ulteriori approfondimenti puoi leggere questo articolo https://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes
Arrotondamento dei decimali di colonne datetime
Guardiamo del codice d’esempio che ci aiuterà a comprendere le tipologie di errori che possiamo commettere se non consideriamo le regole di arrotondamento dei decimali.
Per prima cosa creiamo una tabella temporanea con una colonna di tipo datetime, costruita convertendo esplicitamente una data. Tale conversione avrà l’effetto di aggiungere l’orario 00:00:00.000.
SELECT CONVERT(Datetime,'20190101',112) AS CampoDateTime
INTO #test;
Vediamo ora cosa succede se filtriamo le due query utilizzando due sintassi leggermente diverse.
SELECT COUNT(*)
FROM #test
WHERE CampoDateTime <= '20181231 23:59:59.999';
SELECT COUNT(*)
FROM #test
WHERE CampoDateTime < '20190101';
Queste query restituiscono (probabilmente inaspettatamente) risultati differenti.
Infatti, se confrontato con un datetime, il valore ‘20181231 23:59:59.999‘ viene arrotondato alla mezzanotte del 1 gennaio 2019 e di conseguenza risulta uguale al valore presente nella tabella.
Nell’ultima query invece, stiamo considerando correttamente le righe con valori strettamente minore del 1 gennaio 2019.
Comprendere tale logica è molto importante per scrivere correttamente query frequenti, come quelle per estrarre “tutte le fatture del 2018”, evitando l’utilizzo di funzioni come Month e Year per ragioni di performance.
A tal fine, per evitare qualsiasi problema legato al tipo dei dati, il mio consiglio è quello di utilizzare sempre un doppio filtro con un maggiore uguale a sinistra e un minore stretto a destra. Ad esempio possiamo scrivere
SELECT *
FROM Fatture
WHERE DataFattura >= '20180101'
AND DataFattura < '20190101';
e questo risulterà sempre corretto per ogni tipologia di dato.
Altre peculiarità di datetime e confronto con datetime2
È importante sapere che una colonna datetime non accetta valori precedenti il 1 Gennaio 1753. Inoltre ogni valore memorizzato in una colonna datetime consuma ben 8 bytes.
Le colonne di tipo datetime2 non hanno invece limiti inferiori di salvataggio, la prima data disponibile è il 1 gennaio dell’anno 1. Inoltre di default accettano fino a 7 cifre decimali di secondi evitando il problema di approssimazione presentato nel paragrafo precedente sul datetime. Inoltre specificando in fase di creazione datetime2(2) avremo solo 2 cifre per i decimi di secondo, ma per ogni valore occuperemo solamente 6 bytes (ben 2 bytes in meno rispetto a valori datetime).
Gli aspetti qui descritti fanno riferimento a un database SQL Server. In ambiente Oracle tale situazione merita ulteriori approfondimenti in quanto già le colonne di tipo date memorizzano informazioni contenenti ore, minuti e secondi.
Continua a imparare
- Segui la lezione successiva su come formattare le date in stringhe
- Torna all’indice delle lezioni
- Visita la pagina del mio videocorso avanzato su T-SQL e performance