Nessun risultato. Prova con un altro termine.
Guide
Notizie
Software
Tutorial

Lavorare con il campo data in SQL Server

Come utilizzare al meglio i formati Datetime e Smalldatetime.
Come utilizzare al meglio i formati Datetime e Smalldatetime.
Link copiato negli appunti

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:

  1. 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).
  2. 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
colonna

Tipo
di dati

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
secolo (yy)

Con
secolo (yyyy)

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:

  1. yy (anno: 1753-9999)
  2. qq (quarto di anno: 1-4)
  3. mm (mese: 1-12)
  4. dy (giorno dell'anno: 1-366)
  5. dd (giorno: 1-31)
  6. wk (settimana: 1-53)
  7. dw (giorno della settimana: 1-7 o Dom-Sab)
  8. hh (ora: 0-23)
  9. mi, n (minuto: 0-59)
  10. 10.  ss, s (secondo: 0-59)
  11. 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:

  1. Dateadd: Restituisce un nuovo valore di data in funzione
    dell'intervallo aggiunto alla data specificata.
    Sintassi: DATEADD ( partedidata, valore_numerico, data)
  2. Datediff: Restituisce un valore numerico corrispondente
    al numero di intervalli di tempo tra due valori Date.
    Sintassi: DATEDIFF (partedidata, data_iniziale, data_finale )
  3. Datename: Restituisce un stringa di caratteri che identifica la parte di data di una specifica data.
    Syntax: DATENAME ( parte_di_data, data )
  4. Datepart: Restituisce un intero che identifica la parte
    di data di una specifica data.
    Sintassi: DATEPART ( parte_di_data, data )
  5. Day: Restituisce un intero che rappresenta il giorno
    di una specifica data.
    Sintassi: DAY ( data )
  6. Getdate: Restituisce la data corrente, nel formato di
    memorizzazione interna di SQL Server, è la data di sistema.
    Sintassi: GETDATE ( )
  7. Month: Restituisce un intero che rappresenta il mese
    di una specifica data.
    Sintassi: MONTH ( data )
  8. 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:

  1. Per range di data: tutti gli ordini tra il 20 giugno 1999 ed il 1 Gennaio 2005 o tra ieri e oggi
  2. 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

Screenshot

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.


Ti consigliamo anche