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

Video Corso Data Analysis completo

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

Torna in alto
Torna su