Convertire le Date

Spesso capita di dover gestire, o convertire dati rappresentanti una data/ora che tipicamante si trovano immagazzinati tramite campi di tipo stringa.

Utilizzando DB progettati qualche anno fa', non è difficile incontrare tabelle con campi che contengono dati di tipo data/ora utilizzando i formati più disparati e a volte quelli con le date vanno in coppia con quelli con le ore.
A titolo d'esempio riporto una breve lista dei più diffusi:

Campo di tipo Stringa: "YYYY-MM-DD hh:mm:ss"
Campo di tipo Stringa: "YYYYMMDD"
Campo di tipo Stringa: "DD/MM/YY"
Campo di tipo Stringa: "DD/MM/YY hh.mm.ss"
Campo di tipo Stringa: "YYMMDD"
Campo di tipo Stringa: "YYYYMMDDhhmmss"
Campo di tipo Intero: YYYYMMDD
Campo di tipo Intero: hhmmss

I primi due esempi della lista rappresentano i casi migliori perchè utilizzano rispettivamente la forma canonica ODBC e il formato ISO, ma in generale, quando il formato utilizzato rientra in quelli "standard" è possibile chiamare la funzione Convert() per passare da Stringa a DateTime.

Di seguito riporto alcuni esempi:

-- Su SQLServer 2005 esiste solo il tipo DateTime
Select CONVERT(DateTime, '2009-09-14 17:32:45', 120)
Select CONVERT(DateTime, '20090914', 112)
Select CONVERT(DateTime, '14/09/2009 17:32:45', 103)
Select CONVERT(DateTime, '14-09-2009', 105)
Select CONVERT(DateTime, '17:32:45', 108)
Select CONVERT(DateTime, Cast(20090914 As varchar(8)), 112)

-- Su SQLServer 2008 sono stati introdotti i tipi Date e Time
Select CONVERT(DateTime, '2009-09-14 17:32:45', 120)
Select CONVERT(Date, '20090914', 112)
Select CONVERT(DateTime, '14/09/2009 17:32:45', 103)
Select CONVERT(Date, '14-09-2009', 105)
Select CONVERT(Time, '17:32:45', 108)
Select CONVERT(Date, Cast(20090914 As varchar(8)), 112)

Quando si deve trattare formati più esotici, solitamente, si eseguono due passaggi: prima si estraggono le varie parti (Giorno, Mese, Anno, Ora, Minuti, Secondi), poi si calcola la data/ora risultante.

Un caso particolare che si presta all'utilizzo di diverse tecniche di conversione è il dato di tipo TIME storato in un campo di tipo INT:

Declare @TimNM INT
Select @TimNM = 153223 -- Ore 15:32:24

Tale dato non può essere convertito direttamente con la funzione Convert() come si potrebbe fare con una data, quindi bisogna scrivere qualche linea di codice.

Seguono varie soluzioni scritte "per esteso" che però possono anche essere "compattate".

Concatenazione di Stringhe

Declare @TimNM int
Declare @TimDT DateTime -- Con SQL 2008 si può usare [Time]
Declare @TimVC VarChar(8)

Select @TimNM = 153223 -- Ore 15:32:24

Select @TimVC = CAST(@TimNM As VarChar(6))
If @TimNM < 100000 -- Per le ore < 10:00:00
    Set @TimVC = '0'+@TimVC

Select @TimVC = SubString(@TimVC,1,2) + ':' +
                  SubString(@TimVC,3,2) + ':' +
                    SubString(@TimVC,5,2)

Select @TimDT = CONVERT(Time, @TimVC, 108)
Select @TimDT -- Visualizza il Risultato

Scomposizione e Ricomposizione

Declare @TimNM int
Declare @TimDT DateTime -- Con SQL 2008 si può usare [Time]
Declare @Hour int, @Min int, @Sec int

Select @TimNM = 153223 -- Ore 15:32:24

Select @Hour = @TimNM / 10000,
       @Min = @TimNM % 10000 / 100,
       @Sec = @TimNM % 100

Select @TimDT = DateAdd(Hour, @Hour,
                  DateAdd(Minute, @Min,
                    DateAdd(Second, @Sec, 0)))
Select @TimDT -- Visualizza il Risultato

Ore:Min:Sec → Totale Secondi → Frazione di Giorno

Declare @TimNM int
Declare @TimDT DateTime

Select @TimNM = 153223 -- Ore 15:32:24

Select @TimDT = Cast((@TimNM / 10000) * 3600 +
(@TimNM % 10000 / 100) * 60 +
(@TimNM % 100) As Float) / 86400.0
Select @TimDT -- Visualizza il Risultato

Ore:Min:Sec → Totale Secondi → Frazione di Giorno (Ottimizzata)

Declare @TimNM int
Declare @TimDT DateTime

Select @TimNM = 153223 -- Ore 15:32:24

Select @TimDT = Cast(@TimNM * 9 +
@TimNM % 10000 * 6 +
@TimNM % 100 * 10 As Float) / 2160000.0
Select @TimDT -- Visualizza il Risultato

Gli ultimi due esempi sono molto simili: si basano entrambi sul fatto che il tipo DateTime internamente utilizza un dato di tipo Float in cui la parte intera rappresenta il numero di giorni trascorsi a partire dal 01/01/1900 e la parte frazionaria si ottiene dividendo i secondi trascorsi a partire dalla Mezza Notte per il numero totale di secondi in un giorno (86400).

Il secondo esempio consente di risparmiare qualche ciclo di clock perché richiede due divisioni in meno. Per i più curiosi, segue la dimostrazione della correttezza di tale soluzione a partire dalla soluzione precedente.

Indico con @DT il risultato di tipo DateTime della conversione.
Calcolo il totale dei secondi, poi ricaviamo la "frazione di giorno"
@DT = (Ore * 3600 + Minuti * 60 + Secondi) / 86400

Indico con T il dato numerico che contiene le ore da convertire.
Le Ore si ottengono come divisione intera di T per 10000.
I Minuti si ottengono come divisione intera del Modulo di T a 10000 per 100.
I Secondi si ottengono come Modulo di T a 100.

@DT = (
(T / 10000) * 3600 +
(T % 10000 / 100) * 60 +
T % 100
) / 86400.0

Elimino la "necessità" del troncamento operato dalla divisione Intera:
@DT = (
((T - T % 10000) / 10000) * 3600 +
((T % 10000 - T % 100) / 100) * 0.6 +
T % 100
) / 86400.0

Elimino le divisioni Intere:
@DT = (
(T - (T % 10000)) * 0.36 +
(T % 10000 - T % 100) * 0.6 +
T % 100
) / 86400.0

Espando le sottrazioni:
@DT = (
T * 0.36 - T % 10000 * 0.36 +
T % 10000 * 0.6 - T % 100 * 0.6 +
T % 100
) / 86400.0

Raccolgo:
@DT = (T * 0.36 + T % 10000 * 0.24 + T % 100 * 0.4) / 86400.0

Trasformo i valori decimali in Interi moltiplicando i termini per 25:
@DT = (T * 9 + T % 10000 * 6 + T % 100 * 10) / 2160000.0

Semplice, No?

Come ultima chicca riporto il codice per "costruire" un dato di tipo DateTime2 introdotto da SQLServer 2008.
Tale dato, internamente è rappresentato come nello schema seguente:

/------DateTime2-----\ 0x07 0000000000 F40000 Prec \--Time--/ \Date/ Time = N. di decimi di microsecondo dalla Mezzanotte Date = N. di giorni dal primo gennaio dell'anno 0001

I numeri binari, però sono rappresentati usando la convenzione little-endian, mentre i numeri interi (int e bigint) usano la convenzione big-endian, per cui occorre "ribaltarli".
Questa procedura ha soltanto scopo ditattico e ne sconsiglio l'utilizzo in ambiente "reale" in quanto se necessario si può raggiungere lo scopo con passaggi intermedi tramite il tipo DateTime standard.

Declare @Dn Int
Declare @Tn BigInt
Declare @DTM DateTime2
Declare @DTV Binary(8)
Declare @DTB VarBinary(9)
Declare @I Int

Select @Dn = 733664,     -- 2009-09-15
       @Tn = 36611230000 -- 01:01:01.1230000

Set @DTV = CAST(@Dn As Binary(3)) + CAST(@Tn As Binary(5))

Select @DTB = 0x7, @I = 8
While @I > 0
    Select @DTB = @DTB + SUBSTRING(@DTV, @I, 1),
             @I = @I - 1

Set @DTM = Cast(@DTB As DateTime2)

Select @DTB, @DTM -- Visualizza il Risultato

Tutte le Utility