SQL Server: filtro su campi datetime

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 il 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.

 

Attenzione: il formato YYYY-MM-DD hh:mi:ss.mmm con il trattino che separa l’anno dal mese e dal giorno non è language neutral. Nella lingua italiana ad esempio vengono invertiti giorni e mesi, come mostrato da questo esempio:

SELECT 
   @@LANGUAGE AS Lingua,
   CONVERT(DATETIME,
   '2020-03-01 15:12:33.112') 
                     AS Data;

L’output sarà:

Lingua            Data

Italiano           2020-01-03 15:12:33.113

 

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') 
        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.

 

Corsi correlati

Se cerchi un corso per fare un upgrade del tuo SQL e diventare un vero esperto leggi la scheda del nostro corso su SQL Server e il linguaggio T-SQL.

Torna su