UGISS User Group Italiano SQL Server
Utilizzo corretto del tipo di dato datetime o smalldatetime
Uno dei problemi che più affligge chi si avvicina a SQL Server è l’utilizzo delle date.
Il campo datetime di SQL (oppure il più piccolo smalldatetime) sembra che a volte si comporti in modo misterioso e non sempre sembra essere chiaro come interpreta i valori che gli immettiamo: il formato è giorno-mese-anno, oppure mese-giorno-anno, oppure altro ancora?
Per evitare qualsiasi problema di questo tipo è sufficiente inserire e pensare alle date utilizzando il formato ISO annomesegiorno, senza separatori. Oggi, quindi, si scriverebbe come:
20050117
In questo modo, indipendentemente dalle impostazioni di localizzazione, Sql Server interpreterà correttamente anno, mese e giorno.
Spero che questo riesca a chiarire i dubbi sull’argomento, che ultimamente, sul ng di SQL stanno uscendo spesso. Tale problema, per chi sviluppa utilizzando ADO.NET non è percepito – se si utilizzano correttamente i parametri, ragione in più per farlo – ma è qualcosa che ci si deve abituare a trattare se si scrive anche del codice T-SQL.
Appena ho un’altra manciata di minuti liberi scrivo anche due righe sulla gestione del tempo con SQL, altra questione tipicamente problematica: ogni data inserita prevede, anche se non specificato dall’utente, l’inserimento dei valori relativi ad ora, minuti, secondi e millisecondi. La data sopra riportata, quindi, per SQL Server è
20050117 00:00:00.000
cosa che ovviamente può dare problemi se non se ne tiene conto adeguatamente.
Per chiudere il cerchio sull’argomento "Date e SQL Server" è bene, anzi è obbligatorio, parlare anche degli orari. Non è un caso che il tipo di dato si chiami datetime. Come detto in precedenza, SQL Server non è in grado di memorizzare una data senza appiccicarci anche l’orario.
Questo significa che se si specifica una data senza un orario, SQL Server mette quello di default, ossia 00:00:00.000; per questo motivo il confronto fra date può sembrare difficoltoso.
Una query molto semplice come
SELECT * FROM Ordini WHERE DataOrdine = ‘20050117’
può diventare foriera di dubbi e problemi. Gli unici ordini restituiti da questa query, infatti, saranno gli ordini fatti esattamente a mezzanotte…che probabilmente non saranno poi molti, se, quando abbiamo immeso l’ordine, abbiamo semplicemente specificato un banalissimo GETDATE() (Questo comando restituisce la data corrente, ora compresa. Il suo reciproco in .NET è DateTime.Now).
Se non ci serve la parte relativa all’ora è bene eliminarla. Come?
Semplice:
SELECT CONVERT(CHAR(8), GETDATE(), 112)
La query di esempio sopra riportata, quindi, dovrebbe diventare:
SELECT * FROM Ordini WHERE CONVERT(CHAR(8), DataOrdine , 112) = ‘20050117’
Che funziona anche bene, ma non è certo il massimo dal punto di vista delle prestazioni, in quanto inibisce l’uso degli indici. Meglio essere più furbi allora e utilizzare due soluzioni:
1) Se l’ora non ci serve non memorizziamola, mettiamo solo la data, cosi che l’ora sia sempre 00:00:00.000
2) Se l’ora invece l’abbiamo memorizzata la nostra query può essere riscritta in questo modo:
SELECT * FROM Ordini WHERE DataOrdine BETWEEN ‘20050117 00:00:00.000’ AND ‘20050117 23:59:59.997’
Occhio all’orario finale! I millesimi di secondo non sono 997 a caso, ma sono l’ultimo valore utile per SQL Server. SQL Server, infatti, è in grado di memorizzare le date con una precisione di 3 millesimi di secondo. Se avessi messo 998 o 999, la data sarebbe diventata 20050118, procucendo quindi risultati sbagliati.
Un’ultima cosa riguarda la gestione del solo orario: l’operatore CONVERT permette anche di ottenere, da un valore datetime, solo l’ora, utilizzando un diverso codice per il parametro "style" (l’ultimo). Ad esempio:
SELECT CONVERT(CHAR(12), GETDATE(), 114)
restituisce l’orario in questo formato:
hh:mm:ss.mmm
Per tutta la lista dei possibili valori di style, fare riferimento qui:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
E con questo ho detto tutto! (Almeno tutto ciò che a livello pratico serve!)
Tirando le somme, quindi, nel caso in cui si voglia specificare una data ed un’ora che siano sempre comprese correttamente da SQL Server (senza quindi avere problemi dovuti alla localizzazione del formato) è bene specificarla così:
YYYYMMDD hh:mm:ss.nnn
oppure, se si vogliono separare le cifre della data
YYYY-MM-DDThh:mm:ss.nnn
Lo standard ISO 8601
Cerchiamo di rispondere all’annosa domanda:l formato YYYYMMDD è uguale a YYYY-MM-DD?
SQL Server supporta il formato ISO 8601 per la specifica dei valori di data e ora.
L’utilità di utilizzare tale formato è che l’interpretazione della data espressa è indipendente dalla localizzazione (come detto negli articoli precedenti, in cui viene usato il formato ISO yyyymmdd). L’elenco completo dello standard lo si trova qui:
http://www.mcs.vuw.ac.nz/technical/software/SGML/doc/iso8601/ISO8601.html
e da una lettura dello stesso si può dedurre che i pattern yyyymmdd e yyyy-mm-dd dovrebbero essere trattati in modo identico.
Per SQL Server, però, il formato ISO è definito solamente dal seguente pattern:
yyyy-mm-ddThh:mm:ss[.nnn]
Questo significa che pensare che i formati yyyy-mm-dd e yyyymmdd siano identici, quando si parla di SQL Server, è errato.
I BOL sono chiari (ma non a sufficienza):
"To use the ISO 8601 format, you must specify each element in the format. This also includes the punctuation marks that are shown in the format."
Tradotto in pratica, l’esempio che dimostra il funzionamento dei vari formati è questo:
SET DATEFORMAT DMY
SELECT CAST(‘20060501’ as datetime) — Corretto: 1 Maggio 2006
SELECT CAST(‘2006-05-01T00:00:00’ as datetime) — Corretto: 1 Maggio 2006
SELECT CAST(‘2006-05-01’ as datetime) — Sbagliato: 5 Gennaio 2006
SELECT CAST(‘2006-05-01 00:00:00’ as datetime) — Sbagliato: 5 Gennaio 2006
L’utilizzo del SET DATEFORMAT è necessario per dimostrare l’errore, in quanto, se si usasse invece il formato MDY (quello inglese per intenderci), l’errore non si presenterebbe e la data verrebbe riconosciuta correttamente anche in presenza di una stringa che non rappresenta un formato ISO riconosciuto da SQL Server.
Altri articoli legati all’utilizzo di datetime
SQL Server 2005 Development Guidelines
http://msdn.microsoft.com/it-it/library/cc185069.aspx#ID0ECF
