janvier
2011
Un article de zinzineti
SQL SERVER 2008 dispose de six types pour stocker les données date/heure :
¤ DATETIME
¤ SMALLDATETIME
¤ DATE
¤ TIME
¤ DATETIME2
¤ DATETIMEOFFSET
Les types DATETIME,SMALLDATETIME sont disponibles depuis les premières versions de SQL SERVER.
Par contre les types DATE,TIME,DATETIME2,DATETIMEOFFSET sont disponibles qu’à partir de SQL SERVER 2008.
¤ Pour le type DATETIME (stocké sur 8 octets) la date (date de référence 1er janvier 1900) est stockée sur 4 octets
et l’heure (en clock-ticks) est stockée sur 4 octets.
¤ Pour le type SMALLDATETIME (stocké sur 4 octets) la date (date de référence 1er janvier 1900) est stockée sur 2 octets
et l’heure (en minute pas en clock-ticks comme dans datetime) est stockée sur 2 octets.
¤ Pour le type DATETIME2 les choses ne sont pas simples, car :
• la date de référence est le 1 janvier 0001
• l’heure (en secondes) est stockée sur un nombre d’octets variable selon la précision
Examinons en détails comment SQL SERVER gère les types DATETIME
Soit la requête
DECLARE @dt datetime = '2011-01-12 19:38:41.250'
SELECT CONVERT(binary(8),@dt) AS [INTERNAL DATETIME];
Résultat
—————
[INTERNAL DATETIME]
—————
0x00009E690143BC77
Les 4 premeiers octets stockent la date et les 4 derniers octets stockent l’heure.
[INTERNAL DATETIME] DATE(hexadecimal) HEURE(hexadecimal)
————— ————— —————
0x00009E690143BC77 00009E69 0143BC77
La conversion en décimal donne :
SELECT CONVERT(int,0x00009E69) AS [DATE(decimal)]
,CONVERT(int,0x0143BC77) AS [HEURE(decimal)]
Résultat
——————-
[DATE (decimal)] [DATE (decimal)]
————— —————
40553 21216375
Pour le type DATETIME la date de référence est le 1er janvier 1900, celà veut dire que la valeur 40553 indiquée dans la colonne [DATE(decimal)], représente le nombre de jour entre la date stockée le 1er janvier 1900. Pour trouver la date stockée il suffit de faire
SELECT DATEADD(dd,40553,'1900-01-01')
Ce qui donne la date : 2011-01-12
Pour le type DATETIME,la valeur 21216375 de la colonne [HEURE(decimal)] représente le nombre de ticks (ou ticks-clock) après minuit.
Rappelons que sous SQL SERVER, un tick = 1/300 seconde
1 tick —-> 1/300 seconde
21216375 ticks —-> X
X = 21216375 * 1/300 = 70721.25 secondes = 70721 secondes + 0.25 seconde
70721 secondes 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
En tenant compte de la fration de seconde (0.25 seconde) on a donc :
[Heure] [Minute] [Seconde]
——– ——– ——–
19 38 41.25
La valeur ‘2011-01-12 19:38:41.250′ que nous avons stocker dans la variable n’est donc pas mémorisée telle quelle par SQL SERVER.
la date est stockée comme un entier (nombre de jours entre la date saisie et le 1er janvier 1900) et l’heure stockée comme nombre de ticks à partir de minuit.
On peut procéder de la même façon avec type SMALLDATETIME , la différence est que le type SMALLDATETIME est stocké sur 4 octets, la date (date de référence 1er janvier 1900) est stockée sur 2 octets et l’heure (en minute pas en clock-ticks comme dans datetime) est stockée sur 2 octets.
What You See Is Not What Is Stored 
/!\ PS : Microsoft déconseille l’utilisation de DATETIME sous SQL SERVER 2008 +
————————————————-
Etienne ZINZINDOHOUE
————————————————-
Merci pour la remarque
What You See Is Not What IS StoreD !!!
quand on s’essaie à l’anglais autant le faire correctement