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,99999996Toute 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 ValeurReellementStockeeRé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 0x0000A05400000000Remarquez 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'); GOAttention : 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.CoursRé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.000Notez 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.