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 e il 31 Dicembre 9999 ed occupa uno spazio di 8 byte (fornisce una precisione al trecentesimo di secondo). - Smalldatetime.
Ammette valori compresi tra il 1 Gennaio 1900 e il 6 Giugno 2079 ed occupa uno spazio di 4 byte (fornisce 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 (data di riferimento di Sql Server) - 4 byte per l'ora del giorno, espressa come numero di millisecondi dopo la
mezzanotte
Al contrario i valori smalldatetime archiviano data e ora con una precisione inferiore ed occupano solo 4 byte, così suddivisi:
- 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 minori o uguali di 29,998 secondi vengono arrotondati per difetto al minuto precedente, mentre i valori uguali o maggiori di 29,999 vengono arrotondati per eccesso al minuto successivo, ad esempio:
--restituisce l'ora come 12:35
SELECT CAST('2000-08-05 12:35:29.998' AS smalldatetime)
GO
--restituisce l'ora come 12:36
SELECT CAST('2000-08-05 12:35:29.999' AS smalldatetime)
GO
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:
Use tempdb
Go
CREATE TABLE T_PROVA (Data DateTime) '- Creiamo una tabella con una colonna datetime
Go
--L'inserimento fallisce perchè abbiamo inserito una data minore del 1 gennaio
1753
INSERT INTO T_PROVA VALUES ('17521231')
--L'inserimento fallisce perchè abbiamo inserito una data non valida, non esiste
il 32 gennaio 2005!
INSERT INTO T_PROVA VALUES ('20050132')
--L'inserimento fallisce perchè abbiamo inserito una data non valida, non esiste
la data pippoplutoepaperino!
INSERT INTO T_PROVA VALUES ('pippoplutoepaperino')
Ogni volta che tentiamo di inserire una data non valida, SQL Server ci invia
il seguente messaggio:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
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:
Use tempdb
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 o di localizzazione siano vitali a SQL Server per interpretare in modo corretto le stringhe costanti che vengono immesse come date.
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), che è cablato nel codice e pertanto non è memorizzato in 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:
CONVERT ( tipo_dati [ ( lunghezza ) ], espressione [, stile
] )
--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 sono riportati nella tabella sottostante:
|
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 utilizzati per convertire le stringhe di caratteri in valori datetime che i formati di output visualizzati quando convertiamo i valori datetime in stringhe di caratteri.
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:
Use pubs
Go
--Esempio di conversione di stringhe in valori datetime, per l'inserimento in
tabelle
CREATE TABLE T_PROVA
(
DATA DATETIME
)
GO
SET NOCOUNT ON
-- Inseriamo una data in formato US
INSERT INTO T_PROVA SELECT CONVERT(DATETIME,'05/08/2004',101)
-- Inseriamo una data in formato UK
INSERT INTO T_PROVA SELECT CONVERT(DATETIME,'08/05/2004',103)
-- Inseriamo una data in formato ISO STANDARD
-- Rimane il metodo migliore, perché non è influenzato in alcun modo dalle
-- impostazioni di localizzazione (vedi langid)
INSERT INTO T_PROVA SELECT CONVERT(DATETIME,'20040508',112)
Go
/* Visualizziamo i risultati */
SELECT DATA FROM T_PROVA
--Esempi di utilizzo di stili per la formattazione di output di date
INSERT INTO T_PROVA SELECT CONVERT(DATETIME,'05/08/2004',101)
Go
/* Visualizziamo i risultati delle selezioni dei vari stili */
-- Formato US
SELECT CONVERT(VARCHAR,DATA,101) [Data F. US] FROM T_PROVA
-- Formato UK
SELECT CONVERT(VARCHAR,DATA,103) [Data F. UK] FROM T_PROVA
-- Formato ISO
SELECT CONVERT(VARCHAR,DATA,112) [Data F. ISO] FROM T_PROVA
--Elimino la tabella...
DROP TABLE T_PROVA
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 (anno: 1753-9999)
- qq (quarto di anno: 1-4)
- mm (mese: 1-12)
- dy (giorno dell'anno: 1-366)
- dd (giorno: 1-31)
- wk (settimana: 1-53)
- dw (giorno della settimana: 1-7 o Dom-Sab)
- hh (ora: 0-23)
- mi, n (minuto: 0-59)
- 10. ss, s (secondo: 0-59)
- ms (millisecondo: 0-999)
È 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: Restituisce un nuovo valore di data in funzione
dell'intervallo aggiunto alla data specificata.
Sintassi: DATEADD ( partedidata, valore_numerico, data) - Datediff: Restituisce un valore numerico corrispondente
al numero di intervalli di tempo tra due valori Date.
Sintassi: DATEDIFF (partedidata, data_iniziale, data_finale ) - Datename: Restituisce un stringa di caratteri che identifica la parte di data di una specifica data.
Syntax: DATENAME ( parte_di_data, data ) - Datepart: Restituisce un intero che identifica la parte
di data di una specifica data.
Sintassi: DATEPART ( parte_di_data, data ) - Day: Restituisce un intero che rappresenta il giorno
di una specifica data.
Sintassi: DAY ( data ) - Getdate: Restituisce la data corrente, nel formato di
memorizzazione interna di SQL Server, è la data di sistema.
Sintassi: GETDATE ( ) - Month: Restituisce un intero che rappresenta il mese
di una specifica data.
Sintassi: MONTH ( data ) - Year: Restituisce un intero che rappresenta l'anno
di una specifica data.
Sintassi: YEAR ( data )
SQL Date: tutti gli esempi delle funzioni
Ecco alcuni esempi di utilizzo di queste funzioni:
/* Imposto la lingua ad Italiano per la sessione corrente del
QA */
SET LANGUAGE 'Italian'
/* Assegno la data corrente ad una variabile T-SQL */
DECLARE @DATA DATETIME
SELECT @DATA = GETDATE()
SELECT GETDATE() [Data Corrente]
SELECT DATEADD(DD, 7, @DATA) [Aggiungo 7 giorni alla data di oggi]
SELECT DATEDIFF(DD,'20040101',@DATA) [Numero di giorni dal 01-01-2004]
SELECT DATENAME(MM, @DATA) [Nome del Mese]
SELECT DATEPART(WK, @DATA ) [Numero della settimana]
SELECT DAY (@DATA) [Giorno]
SELECT MONTH(@DATA) [Mese]
SELECT YEAR(@DATA) [Anno]
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: tutti gli ordini tra il 20 giugno 1999 ed il 1 Gennaio 2005 o tra ieri e oggi
- Per data esatta: tutte le spedizioni effettuate il 10 Luglio 2005
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:
Use Pubs
Go
/* Genera 1001 righe di valori datetime, partendo dalla data corrente
*/
CREATE TABLE T_PROVA (
TESTO VARCHAR(32),
DATA DATETIME
)
Go
DECLARE @COUNT INT
SET @COUNT = 1000
WHILE @COUNT>=0 BEGIN
INSERT INTO T_PROVA VALUES(CAST(@COUNT AS VARCHAR),GETDATE()-@COUNT)
SET @COUNT = @COUNT - 1
END
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:
Use Pubs
Go
--Impostazione del formato di input atteso da SQL Server: italiano
SET DATEFORMAT dmy
SELECT * FROM T_PROVA WHERE DATA BETWEEN '01/02/2003' AND '01/03/2003'
SELECT @@ROWCOUNT
Go
--Impostazione del formato di input: us_english
SET DATEFORMAT mdy
SELECT * FROM T_PROVA WHERE DATA BETWEEN '02/01/2003' AND '03/01/2003'
SELECT @@ROWCOUNT
--Non mi importa della impostazione di linguaggio
--quando il formato di input è ISO, perché il risultato è sempre quello atteso
SELECT * FROM T_PROVA WHERE DATA BETWEEN '20030201' AND '20030301'
SELECT @@ROWCOUNT
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:
SELECT * FROM T_PROVA WHERE DATA=GETDATE()
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, vediamo ora i diversi modi con cui possibile ottenere un risultato corretto:
--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.
