SQL Server - Démystifier le type DATETIME et utiliser le format ISO 8601


I - Introduction

La manipulation des dates sous SQL Server a toujours été un cauchemar pour les utilisateurs et même pour les développeurs. Dans les forums dédiés à SQL Server, vous rencontrerez une avalanche de questions au sujet de type DATETIME.

Ces difficultés, à mon sens, proviennent, d'une part du fait que les fonctions, fournies par SQL Server, ne sont pas assez évoluées pour une manipulation aisée des dates (des améliorations ont été apportées sous SQL Server 2012 pour combler cette lacune, mais au moment où je rédige cet article, tout le monde n'utilise pas encore SQL Server 2012 ! Aussi je n'aborde pas le sujet.), d'autre part, de la confusion, ou de la non compréhension, du type DATETIME, tel qu'il est perçu par les utilisateurs et même par les développeurs.

Ces difficultés se déclinent sous 2 aspects :
- L'aspect expression des dates. C'est à dire, « comment exprimer, de manière littérale, une constante date ».
- L'aspect présentation des dates. C'est à dire, « comment présenter les dates au format chaîne de caractères pour les besoins de reporting etc ». Exemple « le Lundi 12 octobre 2012 à 15h20 ».

Le présent article traite et met l'accent sur le premier aspect du problème, c.à.d « l'expression et la manipulation des constantes DATETIME sous forme littérale ». J'ai prévu de publier un autre article où je traiterai en détails le 2ème aspect du problème « comment présenter les dates au format chaîne de caractères pour les besoins de reporting ».

II - Démystifier le type DATETIME

Le type DATETIME, comme beaucoup le pensent, à tort, ne stocke pas des informations sur le jour, le mois, l'année, l'heure, les minutes et les secondes etc. Ce n'est pas du tout de cela dont il s'agit. Les données de type DATETIME ressemblent beaucoup aux données type Float ou peuvent être assimilées au type Float, dans le sens où elles stockent les coordonnées d'un instant t, par rapport à des points (ou repères) fixés à l'avance sur un axe, l'axe temporel.

Le type DATETIME est stocké en interne sur 8 octets. Les 4 premiers octets représentent le nombre de jours depuis le 1er Janvier 1900 et les 4 derniers octets représentent le nombre de millisecondes écoulées depuis minuit (00:00). Tout cela pour dire que les données de type DATETIME sont stockées en binaire sur 8 octets et qu'il ne faut pas confondre la représentation interne ou le stockage en binaire, dans la base, des données de type DATETIME, et la présentation, au format texte, des données de type DATETIME.

La présentation, au format texte, des données de type DATETIME, peut varier en fonction des pays et des cultures (exemple, en France on a l'habitude d'écrire 09/02/2012 14:15 alors que d'autres pays on peut présenter la même date comme ceci : Feb 09 2012 02:15 PM). La représentation interne, binaire des dates est, quant à elle, reste identique quelque soit le pays ou la culture.

Lorsque quelqu'un vient vous dire par exemple « J'ai un problème, j'ai récupéré une base de données où les dates sont en anglais !», sachez désormais que cette phrase ne veut rien dire et n'a aucun sens ! Les dates ne sont ni en français, ni en anglais, ni en kurdistan ! Les date sont tout simplement en binaire. La personne parle vraisemblablement du format de présentation des dates, mais souvent les idées sont confuses et les concepts sont mélangés et mal maîtrisés !

Le domaine des valeurs de type DATETIME, que l'on peut représenter, s'étend de la plus petite valeur 01/01/1753 00:00:00 jusqu'à à la plus grande valeur 31/12/9999 23:59:59.997 (997 représente les millisecondes).
La date 01/01/1900 00:00:00.000 représente une date particulière. Elle correspond à la valeur 0 (zéro) dans l'axe temporel décrit ci-dessus.
La requête SQL, ci-dessous, permet d’illustrer l'ensemble de ces propos.
DECLARE @DateDebut DATETIME, @DateZero  DATETIME, @DateFin  DATETIME; 
SET @DateDebut = '1753-01-01T00:00:00.000'; 
SET @DateZero  = '1900-01-01T00:00:00.000'; 
SET @DateFin  = '9999-12-31T23:59:59.997'; 
SELECT @DateDebut AS 'DateDebut', 
       @DateZero  AS 'DateZero', 
       @DateFin   AS 'DateFin', 
       cast(@DateDebut AS FLOAT) AS 'DateDebut_as_Float',   
       cast(@DateZero AS FLOAT) AS 'DateZero_as_Float', 
       cast(@DateFin AS FLOAT) AS 'DateFin_as_Float';
Résultat :
DateDebut            1753-01-01 00:00:00.000         
DateZero             1900-01-01 00:00:00.000         
DateFin              9999-12-31 23:59:59.997
DateDebut_as_Float   -53690 
DateZero_as_Float    0 
DateFin_as_Float     2958463,99999996
Toute tentative pour représenter une date en dehors de l'intervalle [01/01/1753 00:00:00.000 … 31/12/9999 23:59:59.997] se soldera par une erreur. Exemple :
DECLARE @Date DATETIME; 
SET @Date = '1752-12-31T23:59:59.997';
Résultat :
Msg 242, Niveau 16, État 3, Ligne 2
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.
III - Le Type DATETIME n'est pas précis à la milliseconde près !

Même si SQL Server permet d'exprimer des dates avec les millisecondes, ayez à l’esprit que SQL Server n'est pas en mesure d'être précis à la milliseconde près. Les 8 octets réservés pour représenter une valeur de type DATETIME ne permettent pas de représenter toutes les valeurs possibles de l'espace réel temps, en tant qu'espace topologique, sur le plan purement mathématique, avec toute la notion de continuité et de limite. Les concepteurs de SQL Server ont été obligés d'opter pour la représentation du temps au travers un espace discontinu.
Sous SQL Server, les valeurs DATETIME sont arrondies à des incréments de 0.000, 0.003 ou 0.007 secondes, comme indiqué dans le tableau ci-dessous.
---------------------------------------------------
 Valeur littérale          Valeur interne 
 DATETIME                  stockée en mémoire
---------------------------------------------------
1998-01-01T23:59:59.999    1998-01-02T00:00:00.000
---------------------------------------------------
1998-01-01T23:59:59.995    1998-01-01T23:59:59.997
1998-01-01T23:59:59.996
1998-01-01T23:59:59.997
1998-01-01T23:59:59.998
---------------------------------------------------
1998-01-01T23:59:59.992    1998-01-01T23:59:59.993
1998-01-01T23:59:59.993
1998-01-01T23:59:59.994
---------------------------------------------------
1998-01-01T23:59:59.990    1998-01-01T23:59:59.990
1998-01-01T23:59:59.991
---------------------------------------------------
Remarquez que les 4 valeurs, à priori distinctes, (ligne 7,8, 9, et 10) sont en réalité, au sens SQL Server, exactement identiques puisqu'elles ont la même valeur interne (1998-01-01T23:59:59.997) !
Pour vous en convaincre davantage, examiner le résultat de la requête SQL ci-dessous :
SELECT '2012-05-16T23:59:59.991' ValeurDate,
CAST (CAST('2012-05-16T23:59:59.991' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee
UNION
SELECT '2012-05-16T23:59:59.992' ValeurDate,
CAST (CAST('2012-05-16T23:59:59.992' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee
UNION
SELECT '2012-05-16T23:59:59.993' ValeurDate,
CAST (CAST('2012-05-16T23:59:59.993' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee
UNION
SELECT '2012-05-16T23:59:59.994' ValeurDate,
CAST (CAST('2012-05-16T23:59:59.994' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee
UNION
SELECT '2012-05-16T23:59:59.995' ValeurDate,
CAST (CAST('2012-05-16T23:59:59.995' AS DATETIME) AS BINARY (8)) AS ValeurRellementStockee
UNION ALL
SELECT '2012-05-16T23:59:59.996' ValeurDate,
CAST (CAST('2012-05-16T23:59:59.996' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee
UNION ALL
SELECT '2012-05-16T23:59:59.997' ValeurDate,
CAST (CAST('2012-05-16T23:59:59.997' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee
UNION ALL
SELECT '2012-05-16T23:59:59.998' ValeurDate,
CAST (CAST('2012-05-16T23:59:59.998' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee
UNION ALL
SELECT '2012-05-16T23:59:59.999' ValeurDate,
CAST (CAST('2012-05-16T23:59:59.999' AS DATETIME) AS BINARY (8)) AS ValeurReellementStockee
Résultat :
ValeurDate          ValeurReellementStockee
-------------------------------------------
2012-05-16T23:59:59.991  0x0000A053018B81FD
2012-05-16T23:59:59.992  0x0000A053018B81FE
2012-05-16T23:59:59.993  0x0000A053018B81FE
2012-05-16T23:59:59.994  0x0000A053018B81FE
2012-05-16T23:59:59.995  0x0000A053018B81FF
2012-05-16T23:59:59.996  0x0000A053018B81FF
2012-05-16T23:59:59.997  0x0000A053018B81FF
2012-05-16T23:59:59.998  0x0000A053018B81FF
2012-05-16T23:59:59.999  0x0000A05400000000
Remarquez que les 3 lignes (n° 4, 5, 6 ) ont la même valeur binaire réellement stockée en base de données (0x0000A053018B81FE), de même les 4 lignes (n° 7, 8, 9, et 10) ont la même valeur binaire réellement stockée en base de données (0x0000A053018B81FF) !

On peut dire, en conclusion, que le type DATETIME est précis à 0.004 secondes près (soit à 4 millisecondes près). Autrement dit, le type DATETIME ne doit pas être utilisé si la précision requise est strictement inférieure à 4 millisecondes.

IV - Le type DATETIME et le format ISO 8601

Il arrive assez fréquemment d'observer dans les scripts SQL (scripts T-SQL d'installation, scripts de création de procédure stockée, de fonctions, triggers etc.), l'utilisation de constantes dates, exprimées sous forme littérale, en chaîne de caractères. Exemple : '2012-04-19 00:00:00'.

Il faut être très vigilant dans la manière d'exprimer ces constantes dates en chaînes de caractères. Ce sujet, est même, d'après ce que j'ai pu observer, l'origine des centaines pour ne pas dire des milliers de questions posées sur les forums informatiques dédiés à SQL Server.

Pour résumer le problème : « Tout se passe bien sur la base locale ou la base de développement. Les ennuis commencent lorsqu'on déroule les scripts sur la base du client ! Les scripts génèrent des erreurs jamais soupçonnées jusqu'à présent ! ».

Le problème provient généralement du fait que les scripts n'étaient pas rédigés en respectant les règles de l'art et n'étaient pas conçus pour être universels s'affranchissant de toute culture.

Si vous voulez que vos scripts se déroulent sans erreur (et non pas de manière aléatoire !), et ce, quel que soit le contexte de la session utilisateur (culture, pays, langue, format par défaut des dates (DATEFORMAT) etc.), prenez l'habitude, dès à présent, d'utiliser le format ISO 8601.
Le plus tôt possible vous vous habituerez à ce style de date (ISO 8601), mieux ce sera et vous éviterez ainsi beaucoup de problèmes et de désagréments.

En effet, l'utilisation du format ISO 8601, pour exprimer, de manière littérale, les constantes DATETIME, est fortement recommandée. et ce, pour les raisons suivantes :
- Le format ISO 8601 correspond à une norme internationale,
- Le format ISO 8601 ne dépend pas de DATEFORMAT,
- Le format ISO 8601 est multilingue,
- Le format ISO 8601 n'a pas besoin d'une fonction CAST (ou CONVERT) pour convertir la chaine en DATETIME.

La syntaxe du format ISO 8601 est la suivante :
yyyy-mm-ddThh:mm:ss[.mmm]
où :
yyyy       représente l'année sur 4 chiffres (de 1753 à 9999) 
mm         représente le mois sur 2 chiffres  (de 01 au 12) 
dd         représente le jour sur 2 chiffres   (01 au 31) 
T          fait partie intégrante du format (ne pas omettre le T) et indique le début de la partie heure, minute, secondes, millisecondes 
hh         représente les heures sur 2 chiffres  (de 00 à 23)  
mm         représente les minutes sur 2 chiffres (de 00 à 59) 
ss         représente les secondes  sur 2 chiffres (de 00 à 59)  
.          le point est facultatif il indique le début de la partie millisecondes 
mmm        facultatif, représente les millisecondes sur 3 chiffres (de 000 à 997)
Exemple d'utilisation de constantes de dates exprimées dans le format ISO 8601 :
CREATE TABLE dbo.Cours(
  Id  INT NOT NULL,
  DateHeureDebut DATETIME  NULL,
  DateHeureFin   DATETIME  NULL
); 
GO

INSERT INTO dbo.Cours
  (Id, DateHeureDebut, DateHeureFin) 
VALUES 
 (1,  '2012-05-15T08:30:00', '2012-05-15T10:45:00'), 
 (2,  '2012-05-18T09:20:00', '2012-05-18T11:50:00');   
GO  
Attention : N'omettez pas le T et la partie hh:mm:ss. En effet, si vous omettez le 'T' ou la partie hh:mm:ss, exemple '2012-05-15', le script ci-dessus ne marchera pas, ou pire marchera de manière aléatoire selon le contexte et la configuration ! En effet, '2012-05-15' ne représente pas une date au format ISO 8601.
SELECT * FROM dbo.Cours
Résultat :
Id DateHeureDebut         DateHeureFin
1 2012-05-15 08:30:00.000 2012-05-15 10:45:00.000
2 2012-05-18 09:20:00.000 2012-05-18 11:50:00.000
Notez que les scripts ci-dessus, se dérouleront toujours sans erreur, et ce, quel que soit le contexte et/ou la configuration (pays, cultures etc.).

Aucun commentaire:

Enregistrer un commentaire

Remarque : Seul un membre de ce blog est autorisé à enregistrer un commentaire.