Sql Server non possiede tipi dati separati per archiviare i valori di data e ora. Adotta una strategia differente: un tipo dati combinato, denominato datetime, in grado di archiviarli entrambi.
La suddivisione tra Datetime e Smalldatetime
I tipi datetime sono suddivi in due tipologie:
- Datetime.
Ammette valori compresi tra il 1 Gennaio 1753 31 Dicembre 9999 8 byte precisione al trecentesimo di secondo - Smalldatetime.
Ammette valori compresi tra il 1 Gennaio 1900 6 Giugno 2079 4 byte una precisione al minuto
I valori datetime archiviano data e ora con grande precisione, per questo motivo utilizzano 8 byte per la memorizzazione:
- 4 byte per il numero di giorni precedenti o successivi alla data di base,
ovvero il 1° gennaio 1900 - 4 byte per l'ora del giorno, espressa come numero di millisecondi dopo la
mezzanotte
Al contrario i valori smalldatetime
- 2 byte per il numero di giorni successivi al 1° gennaio 1900
- 2 byte per il numero di minuti dopo la mezzanotte
Inoltre per valori smalldatetime
SQL: date rifiutate
I valori non riconosciuti in SQL Server come date comprese tra il 1753 e il
9999 vengono rifiutati, vediamo alcuni esempi di valori non corretti:
Ogni volta che tentiamo di inserire una data non valida, SQL Server ci invia
il seguente messaggio:
Inserire valori di data e tempo in SQL Server
Nel paragrafo precedente, abbiamo visto che l'immissione di date in SQL Server avviene tramite stringhe costanti le quali vengono interpretate e convertite in formato binario.
Una volta che i dati relativi a data e ora sono rappresentati in formato binario, l'esecuzione dei calcoli complessi su date e ore risulta semplice, così come la conversione delle date e delle ore in stringhe durante l'applicazione di formati diversi mediante le funzioni T-SQL.
Il fatto di inserire le date sotto forma di stringhe, implica che le impostazioni di localizzazione di SQL Server influenzino le operazioni di inserimento delle date, vediamo un esempio:
Go
DECLARE @DATA DATETIME
SELECT @DATA = '03/08/04'
Il valore selezionato sarà il 20 Marzo 2004 o 3 Agosto 2004? Come fa SQL Server a capire quale data vogliamo memorizzare? Proviamo a vedere come interpreta la data '03/08/2004'
SELECT @DATA, CONVERT(VARCHAR(20), @DATA)
Sul mio SQL Server il risultato è:
2004-03-08 00:00:00.000 Mar 8 2004 12:00AM
Nel mio caso SQL Server memorizza la data come 8 Marzo 2004, dato che le impostazioni di localizzazione del mio Server Sql sono settate sull' Inglese Americano e quindi il formato data atteso durante l'inserimento è Mese-Giorno-Anno
È ovvio che le impostazione della lingua localizzazione
Ovviamente tutte le impostazioni di localizzazione possono essere modificate
sia a livello di connessione che di sessione tramite apposiste comandi T-SQL.
Le lingue e le impostazioni di localizzazione
In SQL Server è presente un concetto di lingua distinto e separato rispetto
al concetto di impostazioni internazionali di Windows (visibili e configurabili
da pannello di controllo).
Per questo motivo è bene definire un'impostazione della lingua specifica di
SQL Server come langid anziché come lingua o impostazione internazionale.
Un langid consente a SQL Server di stabilire in che modo devono essere eseguite alcune operazioni relative a data e ora, valori monetari e messaggi di sistema. Ogni volta che un utente stabilisce una connessione a SQL Server, esso controlla che esista un langid valido per la connessione, in caso contrario viene assegnato il langid predefinito. I langid di SQL Server e le relative impostazioni sono memorizzati nella tabella syslanguages sulla quale è possibile eseguire interrogazioni utilizzando la procedura memorizzata sp_helplanguage.
Il set di risultati restituiti è il seguente:
|
Nome |
Tipo |
Descrizione |
| langid | smallint | Numero di identificazione della lingua |
| dateformat | nchar(3) | Formato della data |
| datefirst | tinyint | Primo giorno della settimana: 1 per lunedì, 2 per martedì e così via fino a 7 per domenica |
| upgrade | int | Versione dell'ultimo aggiornamento di SQL Server per la lingua specificata |
| name | sysname | Nome della lingua |
| alias | sysname | Nome alternativo per la lingua |
| months | nvarchar(372) | Nomi dei mesi |
| shortmonths | nvarchar(132) | Nomi dei mesi abbreviati |
| days | nvarchar(217) | Nomi dei giorni |
| lcid | int | ID delle impostazioni internazionali di Microsoft Windows NT® relative alla lingua specificata |
| msglangid | smallint | ID del gruppo di messaggi di SQL Server |
Per provare a vedere il contenuto della tabella eseguite nel QA questa istruzione EXEC sp_ helplanguage.
L'unica eccezione è rappresentata dal langid 0 (us_english) syslanguages
Nel prima parte dell'articolo abbiamo introdotto il tipo dati datetime di SQL Server ed analizzato l'influenza delle impostazioni di localizzazione sulle operazioni di inserimento e calcolo con i valori datetime. Ora invece vedremo come effettuare le operazioni di formattazione, conversione e ricerca sui campi data.
Funzioni per la conversione di valori datetime
Il linguaggio T-SQL adotta due funzioni in grado di effettuare la conversione
tra tipi dati differenti: CAST e CONVERT. In particolar modo la funzione CONVERT offre un'ampia gamma di stili di conversione. La sintassi della funzione CONVERT è la seguente:
] )
--Converte la data corrente nel formato Italiano
SELECT CONVERT( VARCHAR, GETDATE(), 105 )
--Restituisce
07-02-2005
Uno stralcio degli stili disponibili per l'utilizzo con la funzione CONVERT
|
Senza |
Con |
Standard |
Input/Output |
| - | 0 or 100 (*) | Default | mon dd yyyy hh:miAM (or PM) |
| 1 | 101 | USA | mm/dd/yy |
| 2 | 102 | ANSI | yy.mm.dd |
| 3 | 103 | British/French | dd/mm/yy |
| 4 | 104 | German | dd.mm.yy |
| 5 | 105 | Italian | dd-mm-yy |
| 6 | 106 | - | dd mon yy |
| 7 | 107 | - | Mon dd, yy |
| 8 | 108 | - | hh:mm:ss |
| - | 9 or 109 (*) | Default + milliseconds | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 | 110 | USA | mm-dd-yy |
| 11 | 111 | JAPAN | yy/mm/dd |
| 12 | 112 | ISO | yymmdd |
NB:
I valori in tabella rappresentano sia i formati di input formati di output
La cosa importante da ricordare è che SQL Server restituisce al client i valori datetime solo come stringhe di caratteri.
Vediamo ora alcuni esempi di utilizzo della funzione CONVERT:
Funzioni per manipolare i valori datetime
Prima di passare in rassegna le funzioni per la manipolazione delle date è
bene comprendere il concetto di "parti di data".
Esse definiscono unità discrete di tempo che possono essere estratte da un valore datetime o utilizzate in calcoli datetime. In SQL Server ne esistono diverse e identificano intervalli temporali compresi tra un millisecondo e un secolo. Ecco l'elenco completo:
- yy
- mm
- dy
- dd
- wk
- dw
- hh
- mi, n
- 10. ss, s
- ms
È bene ricordare che la parte di data riguardante la settimana (wk, ww) riflette le modifiche apportate mediante la funzione SET DATEFIRST ed inoltre che il 1° gennaio di ogni anno definisce il numero iniziale della parte di data settimana. Anche la parte di data giorno della settimana (dw con valori da 1 a 7) dipende dal valore impostato con la funzione SET DATEFIRST, che consente di impostare il primo giorno della settimana.
SQL Date: tutte le funzioni
Vediamo ora l'elenco delle principali funzioni per manipolare le date:
- Dateadd
dell'intervallo aggiunto alla data specificata.
Sintassi - Datediff
al numero di intervalli di tempo tra due valori Date.
Sintassi - Datename
Syntax - Datepart
di data di una specifica data.
Sintassi - Day
di una specifica data.
Sintassi - Getdate
memorizzazione interna di SQL Server, è la data di sistema.
Sintassi - Month
di una specifica data.
Sintassi - Year
di una specifica data.
Sintassi
SQL Date: tutti gli esempi delle funzioni
Ecco alcuni esempi di utilizzo di queste funzioni:
Ricerca all'interno di campi datetime
Nella maggioranza delle applicazioni la ricerca su campi data diventa diventa
fondamentale. Possiamo avere svariate tipologie di ricerca sui campi data, anche
se le principali sono:
- Per range di data
- Per data esatta
Per provare i vari casi elencati creiamo una tabella ad hoc su cui testare
gli esempi che svilupperemo. Vi consiglio inoltre di usare questa tabella per
provare le vostre query in un ambiente di prova per non avere sorprese sgradevoli al momento di mettere in produzione il codice T-SQL! Vediamo il codice:
Ricerca per range di data
Supponiamo di voler cercare tutte le date del mese di Febbraio 2003 per la
tabella T_PROVA, il risultato atteso è di 28 righe. Per sviluppare la query è
consigliato l'operatore BETWEEN:
I risultati sono tutti corretti, ma sono dipendenti (tranne l'ultimo caso)
dalle impostazioni del linguaggio (in questo caso usiamo SET
DATEFORMAT per informare SQL Server sul formato di input della data). Quindi
prima di valutare query simili dobbiamo sempre tenere in considerazione il linguaggio con cui la nostra login, connessione o sessione di SQL Server è impostata.
Ricerca per data esatta
È sicuramente una delle ricerche più comuni e banali ma nasconde alcune insidie. Proviamo a creare una query capace di restituire la riga corrispondente alla data corrente nella tabella T_PROVA generata precedentemente:
Apparentemente semplice, ma il risultato non è scontato! La query non restituisce alcuna riga, eppure tutto è corretto perché la data odierna è sicuramente presente nella tabella.
Anche se ciò è vero non ci sono risultati perché i valori che andiamo a confrontare non sono uguali, o meglio solo in parte.
La data memorizzata nella tabella (al momento in cui ho eseguito lo script
ovviamente) è pari a '2005-02-07 22:03:39.117' mentre la funzione GETDATE() restituisce solamente la stessa parte di data con un tempo in ore minuti e millisecondi logicamente differente.
Per fare un confronto omogeneo dobbiamo eliminare la porzione ore, minuti e millisecondi da entrambe le date
--1
SELECT * FROM T_PROVA WHERE DATEDIFF(DD, DATA, GETDATE()) = 0
--2
SELECT * FROM T_PROVA WHERE FLOOR( CAST(DATA
AS FLOAT) ) = FLOOR( CAST(GETDATE() AS FLOAT) )
--3
SELECT * FROM T_PROVA WHERE CONVERT(VARCHAR(8),DATA,112)
= CONVERT(VARCHAR(8),GETDATE(),112)
--4
SELECT * FROM T_PROVA WHERE YEAR(DATA) = 2005
AND MONTH(DATA) = 2 AND DAY(DATA)=7
--5
DECLARE @D INT
-- Estrapola solo la parte della data come numero intero
-- grazie alla funzione FLOOR, faccio il CAST di GETDATE() a FLOAT
SET @D = FLOOR( CAST(GETDATE() AS FLOAT) )
SELECT * FROM T_PROVA WHERE DATA BETWEEN @D AND
@D + 1
Nel caso n. 5 grazie all'operatore BETWEEN possiamo anche sfruttare un eventuale indice piazzato nella colonna DATA e rendere ancora più performante la ricerca.
Conclusione SQL Server - date
Nell'articolo abbiamo esaminato le insidie nascoste all'interno dei valori datetime ed inoltre imparato a conoscere l'importanza delle impostazione di localizzazione di SQL Server nelle operazioni di calcolo e inserimento delle date. Infine abbiamo descritto le funzioni di conversione e formattazione delle date condensando il tutto nelle query di ricerca sui campi datetime.
