SQL – Etienne ZINZINDOHOUE

Un article de zinzineti    Pas de commentaires

Les nouveaux types DATE,TIME,DATETIME2,DATETIMEOFFSET sont apparus depuis SQL SERVER 2008. Ces types sont différemment gérés comparés aux types DATETIME et SMALLDATETIME :
¤ la date de référence est désormais le 1er janvier 0001 pour ces nouveaux types
¤ l’heure est stockée en secondes sur un nombre d’octets variable selon la précision imposée de façon explicite

Voyons en détails comment fonctionne par exemple le type DATETIME2

Exécutons la requête

DECLARE @dt2 datetime2 = '2011-01-12 19:38:41.2501234'
 
SELECT  cast(@dt2 as datetime2(0))  
UNION
SELECT  cast(@dt2 as datetime2(1))  
UNION
SELECT  cast(@dt2 as datetime2(2))  
UNION
SELECT  cast(@dt2 as datetime2(3))  
UNION
SELECT  cast(@dt2 as datetime2(4))  
UNION
SELECT  cast(@dt2 as datetime2(5))  
UNION
SELECT  cast(@dt2 as datetime2(6))  
UNION
SELECT  cast(@dt2 as datetime2(7))

Résultat
————————–

Voyons les résultats en hexadécimal

DECLARE @dt2 datetime2 = '2011-01-12 19:38:41.2501234'

SELECT cast(cast(@dt2 as datetime2(0)) as binary(7))

UNION

SELECT cast(cast(@dt2 as datetime2(1)) as binary(7))

UNION

SELECT cast(cast(@dt2 as datetime2(2)) as binary(7))

UNION

SELECT cast(cast(@dt2 as datetime2(3)) as binary(8))

UNION

SELECT cast(cast(@dt2 as datetime2(4)) as binary(8))

UNION

SELECT cast(cast(@dt2 as datetime2(5)) as binary(9))

UNION

SELECT cast(cast(@dt2 as datetime2(6)) as binary(9))

UNION

SELECT cast(cast(@dt2 as datetime2(7)) as binary(9))

Résultat
———————–

0x00411401C4330B0000

0x018DCA0AC4330B0000

0x027DE96BC4330B0000

0x03E21E3704C4330B00

0x04D534272AC4330B00

0x05541088A501C4330B

0x064BA3507710C4330B

0x07F26026A9A4C4330B

Remarquons ces résultats sont préfixés des valeurs relatives à la précision sur l’heure

0x00411401C4330B0000

0x018DCA0AC4330B0000

0x027DE96BC4330B0000

0x03E21E3704C4330B00

0x04D534272AC4330B00

0x05541088A501C4330B

0x064BA3507710C4330B

0x07F26026A9A4C4330B

En supprimant les préfixes (c’est à dire les valeurs relatives à la précision sur l’heure) on obtient :

411401C4330B0000

8DCA0AC4330B0000

7DE96BC4330B0000

E21E3704C4330B00

D534272AC4330B00

541088A501C4330B

4BA3507710C4330B

F26026A9A4C4330B

étant donnée que la date est fixe et que la précision agit uniquement sur l’heure, identifions les données date et heure du résultat précédent

initialement(hexadecimal) Heure (hexadecimal) Date (hexadecimal)

—————– ————— ———–

411401C4330B0000 411401 C4330B0000

8DCA0AC4330B0000 8DCA0A C4330B0000

7DE96BC4330B0000 7DE96B C4330B0000

E21E3704C4330B00 E21E3704 C4330B00

D534272AC4330B00 D534272A C4330B00

541088A501C4330B 541088A501 C4330B

4BA3507710C4330B 4BA3507710 C4330B

F26026A9A4C4330B F26026A9A4 C4330B

On peut donc déduire le nombre d’octets utilisés en fonction de la précision sur l’heure :

Datetime2 Nb octets utiliss pour l'heure

———– —————————-

datetime2(0) 3

datetime2(1) 3

datetime2(2) 3

datetime2(3) 4

datetime2(4) 4

datetime2(5) 5

datetime2(6) 5

datetime2(7) 5

Prenons par exemple la première ligne

initialement(hexadecimal) Heure (hexadecimal) Date (hexadecimal)

—————– ————— ———–

411401C4330B0000 411401 C4330B0000

Effectuons une translation pour trouver ces valeurs. La translation consiste à lire deux à deux les caractères en partant de droite vers la gauche, on obtient :

Heure translatée (hexadecimal) Date translatée (hexadecimal)

————— ———–

011441 00000B33C4

Convertissons en décimal, les valeurs hexadecimales translatées. On peut utiliser la culculette (calc.exe) ou simplement la requête suivante :

SELECT CONVERT(int,0x011441) AS [heure_decimal], CONVERT(int,0x0000B33C4)AS [date_decimal]

Résultat
————————

Heure (en decimal) Date (en decimal)

————— ———–

70721 734148

70721 égal au nombre de secondes. Il correspond à quelle heure ?

DECLARE @Secondes INT

SET @Secondes = 70721

SELECT Heure = @Secondes / 3600, Minute = (@Secondes%3600) / 60, Seconde = (@Secondes%60)

Résultat
———————

Heure Minute Seconde

—— —— ——

19 38 41

Ce résultat correspond est bien ce qu’on attend

La valeur présentée dans la colonne Date(en décimale) 734148 égale au nombre de jours qui séparent la date saisie et le 1er janvier 0001. 734148 correspond donc à quelle date ?
La requête suivante, nous donne le résultat

SELECT DATEADD(dd,734148,CAST('0001-01-01' AS datetime2(0)))

Résultat
———————
2011-01-12 00:00:00

En mettant en regard les résultats heure et date on a :

2011-01-12 19:38:41

Qui correspond au résultat de la requête :

DECLARE @dt2 datetime2 = '2011-01-12 19:38:41.2501234'

SELECT cast(@dt2 as datetime2(0))

on peut appliquer la même méthode pour les autres précisions (datetime2(1),datetime2(2), datetime2(3),…,datetime2(7)).

Voilà comment SQL SERVER gère les données de type DATETIME2.

What You See Is Not What Is Stored

/!\ PS : Microsoft déconseille l’utilisation de DATETIME sous SQL SERVER 2008 +

——————————————–
Etienne ZINZINDOHOUE
——————————————–