Lorsqu’il est nécessaire de copier des données depuis une base vers une autre, un méthode pratique consiste à utiliser le générateur de scripts, disponible depuis la version SQL Server 2005 et surtout capable de générer depuis SQL Server 2008 non seulement les scripts des structures mais aussi de générer les instructions de remplissage des tables.
Mais avez-vous déjà cherché à regarder à quoi ressemble le script généré lorsque les données à copier sont de type de date et d’heure ?
Reprenons tout d’abord le contexte. Supposons que nous ayons à générer un script pour copier des données depuis un table quelconque vers une autre base. Pour cela, nous avons un magnifique menu contextuel, hautement configurable.
Je ne vais pas ici détailler les détailler les différentes possibilités et options de cette fonctionnalité, il y en a plus que ce qu’il serait raisonnable de détailler dans un article. Pour ceux qui veulent en savoir un peu plus, je vous invite à regarder ici.
Nous allons ici juste nous concentrer sur le comportement de ce générateur de scripts pour les colonnes de type datetime. En effet, la traduction entre chaînes de caractères (utilisables dans les scripts) et les dates-heures est toujours très problématique, car fortement lié aux préférences linguistiques de l’utilisateur exécutant le script.
Le choix des équipes de développement est d’utiliser une autre présentation des valeurs de type datetime, à savoir le format binaire.
Ce format permet de s’adapter directement au type de données. Le tableau suivant montre le lien presque bijectif entre le caractéristiques de la valeur binaire (longueur et éventuellement premier octet) et le type de données correspondant. Une colonne complémentaire montre l’espace de stockage de la valeur tel qu’indiqué dans l’aide en ligne.
| Type de donnée | Espace de stockage (BOL) | Exemple de donnée | Longueur de la conversion | Premier octet complémentaire |
| date | 3 | 0xA9350B | 3 | |
| datetime | 8 | 0x0000A04E0180461D | 8 | |
| datetime2(0) | 6 | 0x00EA4701A9350B | 7 | 0x00 |
| datetime2(1) | 6 | 0x0123CF0CA9350B | 7 | 0x01 |
| datetime2(2) | 6 | 0x025F1780A9350B | 7 | 0x02 |
| datetime2(3) | 7 | 0x03B5E90005A9350B | 8 | 0x03 |
| datetime2(4) | 7 | 0x0417210932A9350B | 8 | 0x04 |
| datetime2(5) | 8 | 0x05E44A5BF401A9350B | 9 | 0x05 |
| datetime2(6) | 8 | 0x06ECEC908B13A9350B | 9 | 0x06 |
| datetime2(7) | 8 | 0x073B41A973C3A9350B | 9 | 0x07 |
| datetimeoffset(0) | 8 | 0x00EA4701A9350B0000 | 9 | 0x00 |
| datetimeoffset(1) | 8 | 0x0123CF0CA9350B0000 | 9 | 0x01 |
| datetimeoffset(2) | 8 | 0x025F1780A9350B0000 | 9 | 0x02 |
| datetimeoffset(3) | 9 | 0x03B5E90005A9350B0000 | 10 | 0x03 |
| datetimeoffset(4) | 9 | 0x0417210932A9350B0000 | 10 | 0x04 |
| datetimeoffset(5) | 10 | 0x05E44A5BF401A9350B0000 | 11 | 0x05 |
| datetimeoffset(6) | 10 | 0x06ECEC908B13A9350B0000 | 11 | 0x06 |
| datetimeoffset(7) | 10 | 0x073B41A973C3A9350B0000 | 11 | 0x07 |
| smalldatetime | 4 | 0xA04E0577 | 4 | |
| time(0) | 3 | 0x00EA4701 | 4 | 0x00 |
| time(1) | 3 | 0x0123CF0C | 4 | 0x01 |
| time(2) | 3 | 0x025F1780 | 4 | 0x02 |
| time(3) | 4 | 0x03B5E90005 | 5 | 0x03 |
| time(4) | 4 | 0x0417210932 | 5 | 0x04 |
| time(5) | 5 | 0x05E44A5BF401 | 6 | 0x05 |
| time(6) | 5 | 0x06ECEC908B13 | 6 | 0x06 |
| time(7) | 5 | 0x073B41A973C3 | 6 | 0x07 |
Les types de données comportant un paramètre de précision ajoutent un premier octet en plus de ce qui est réellement stocké. Cet octet permet notamment d’empêcher de chercher à stocker de manière involontaire une valeur directement sous forme binaire sans en fait maîtriser la valeur ‘fonctionnelle’.
On remarque d’après ce tableau que la longueur du binaire, éventuellement couplée avec un premier octet complémentaire, permet dans presque tous les cas, de déterminer de manière unique le type de données, ce qui verrouille la transformation en intégrant en quelque sorte le type de données dès la valeur binaire elle-même.
Les seules combinaisons pouvant potentiellement ne pas indiquer sans ambiguïté le type de données sont:
- longueur 8 : ambiguïté entre datetime et datetime(3) / datetime(4). Ce cas est géré par le fait que le premier octet du stockage d’une valeur datetime ne peut pas être supérieure ou égale à 3, car cela dépasserait la plage de valeurs du type datetime. Il n’y a donc pas de problème ici.
- longueur 4 : ambiguïté entre smalldatetime et time(0) / time(1) / time(2). Dans ce cas, il peut par par contre y avoir un réel mélange de valeurs, c’est-à-dire par exemple qu’un binaire descriptif de smalldatetime peut être parfaitement identique à un binaire descriptif de time(0).
Mais cette ambiguïté n’est qu’anecdotique car le générateur de script positionne des conversions explicites dans le script.
INSERT [dbo].[MaTable] ([Id], [Chaine], [DateModif]) VALUES (1, N'Donne chane', CAST(0x0000A04F012FA275 AS DateTime))
Voici donc pour une brève présentation de la conversion entre valeurs binaires et types de données de date et d’heure, et pour leur utilisation dans la génération de scripts d’insertion de données. Certes, ce n’est pas forcément facilement éditable (pour modifier les dates), mais en même temps, ce n’est pas nécessairement le but…