DateTime2 vs DateTime de SQL Server

La documentation MSDN pour datetime recommande l'utilisation de datetime2. Voici leur recommandation:

Utiliser le time, date, datetime2 et
datetimeoffset types de données pour les nouveaux
travail. Ces types aligner avec le SQL
Standard. Ils sont plus mobiles.
time, datetime2 et datetimeoffset
fournir plus de précision en secondes.
datetimeoffset fournit l'heure de la zone
soutien pour déployée à l'échelle mondiale
des applications.

datetime2 a plus grande plage de dates, une plus grande précision fractionnaire par défaut, et en option spécifiée par l'utilisateur de précision. Aussi en fonction définie par l'utilisateur de précision, il peut utiliser moins d'espace de stockage.

datetime2 victoires dans la plupart des aspects à l'exception des (anciennes applications de Compatibilité)

1. plus plage de valeurs
2. mieux Précision
3. petits de l'espace de stockage (si option spécifiée par l'utilisateur précision est spécifiée)

veuillez noter les points suivants

• Syntaxe
  • datetime2[(fractions de secondes précision=> Regardez ci-Dessous la Taille de Stockage)]
• Précision, échelle
  • 0 à 7 chiffres, avec une précision de 100 ns.
  • La précision par défaut est de 7 chiffres.
• Taille De Stockage
  • 6 octets pour la précision de moins de 3;
  • 7 octets pour la précision 3 et 4.
  • Tous les autres précision 8 octets.
• DateTime2(3) ont le même nombre de chiffres que DateTime mais utilise 7 octets de stockage au lieu de 8 octets (SQLHINTS - DateTime Vs DateTime2)
• En savoir plus sur le datetime2(Transact-SQL article MSDN)

source de l'image :
Les SCTM de Self-Paced training Kit (Examen 70-432): Microsoft® SQL Server® 2008 - mise en Œuvre et de Maintenance
Chapitre 3:Tableaux -> Leçon 1: Création de Tables -> page 66

Presque toutes les Réponses et les Commentaires ont été lourdes sur les avantages et de la lumière sur les Inconvénients. Voici un résumé de tous les avantages et les Inconvénients jusqu'à présent, plus certains Inconvénients (#2 ci-dessous) je ne l'ai vu mentionné une seule fois ou pas du tout.

1. POUR:

1.1. Plus conforme aux normes ISO (ISO 8601) (bien que je ne sais pas comment cela entre en jeu dans la pratique).

1.2. Plus de la gamme (1/1/0001 à 12/31/9999 vs 1/1/1753-12/31/9999) (bien que la gamme supplémentaire, et ce, avant l'année 1753, ne sera probablement pas être utilisée, sauf pour ex., historiques, astronomiques, géologiques, etc. apps).

1.3. Correspond exactement à celle de la gamme de .NET DateTime Type de gamme (bien que les deux se reconvertir et vient sans codage spécial si les valeurs sont en deçà de la cible de ce type de gamme et de précision, sauf pour la Con # 2.1 ci-dessous else //erreur de l'arrondissement va se produire).

1.4. Plus de précision (100 nanosecondes aka 0.000 000 à 1 sec. vs 3.33 milliseconde aka 0.003,33 sec.) (bien que l'une précision supplémentaire ne sera probablement pas être utilisée, sauf pour ex., en sciences ou en génie apps).

1.5. Lorsqu'il est configuré pour similaire (comme dans 1 millisec pas "même" (comme dans 3.33 millisec) comme Iman Abidi a revendiqué) précision DateTime, utilise moins d'espace (7 vs 8 octets), mais bien sûr, vous auriez à perdre de la précision de la prestation qui est probablement l'un des deux (l'autre étant de gamme), la plupart des vanté quoique probablement inutiles avantages).

1. CONTRE:

2.1. Lors du passage d'un Paramètre à un .NET SqlCommand, vous devez spécifier System.Data.SqlDbType.DateTime2 si vous pouvez peut-être le passage d'une valeur à l'extérieur de SQL Server DateTime’de la gamme s et/ou de précision, car la valeur par défaut System.Data.SqlDbType.DateTime.

2.2. Ne peut pas être implicitement /facilement converti en virgule flottante numérique (nombre de jours depuis min date-heure) valeur d'effectuer les opérations suivantes à /avec elle dans SQL Server expressions à l'aide de valeurs numériques et des opérateurs:

2.2.1. ajouter ou soustraire du nombre de jours ou d'une partie de la journée. Remarque: l'Utilisation de DateAdd Fonctionner comme une solution de contournement n'est pas anodin lorsque vous avez besoin de considérer plusieurs si pas de toutes les parties de la date-heure.

2.2.2. la différence entre deux dates et heures pour les besoins de “l'âge” de calcul. Remarque: Vous ne pouvez pas simplement utiliser SQL Server DateDiff Fonction au lieu de cela, parce qu'il ne calcule pas age comme la plupart des gens s'attendent à ce que si les deux dates-temps arrive à traverser un calendrier /horloge date-limite d'heure d'unités spécifiées même pour une fraction infime de cette unité, il sera de retour à la différence que 1 de cette unité par rapport à 0. Par exemple, le DateDiff dans Day’s de deux fois seulement 1 milliseconde à part sera de retour 1 vs 0 (jours) si la date et l'heure sont sur les différents jours de calendrier (c'est à dire “1999-12-31 23:59:59.9999999” et“2000-01-01 00:00:00.0000000”). Le même 1 milliseconde différence la date et l'heure si déplacement, de sorte qu'ils ne traversent pas un jour du calendrier, sera de retour un “DateDiff” dans Day’s de 0 (jours).

2.2.3. prendre la Avg de la date et l'heure (dans une Requête d'Agrégation) simplement en convertissant à “Flotter” d'abord et puis de nouveau à DateTime.

REMARQUE: Pour convertir DateTime2 numérique, vous avez à faire quelque chose comme la formule suivante qui assume toujours vos valeurs ne sont pas de moins que l'année 1970 (ce qui signifie que vous perdez de l'ensemble de la gamme extra plus de 217 ans. Remarque: Vous ne pouvez pas être en mesure de régler simplement la formule pour permettre la plage supplémentaire parce que vous pouvez exécuter en numérique les problèmes de débordement.

25567 + (DATEDIFF(SECOND, {d '1970-01-01'}, @Time) + DATEPART(nanosecond, @Time) /1.0E + 9) /86400.0 – Source: “ https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html “

Bien sûr, vous pouvez également Cast à DateTime première (et, si nécessaire, de retour de nouveau à DateTime2), mais vous perdez la précision et la plage (à tous avant l'année 1753) les prestations de DateTime2 vs DateTime qui sont pro les 2 plus grands et aussi dans le même temps pro la 2 moins besoin de ce qui soulève la question de savoir pourquoi l'utiliser lorsque vous perdez de l'implicite /conversions faciles à virgule flottante numérique (nombre de jours) pour l'addition /soustraction /"âge" (vs DateDiff) /Avg calcs prestation qui est un grand un seul en mon expérience.

Btw, le Avg de la date et l'heure est (ou au moins devrait être) un important cas d'utilisation. a) en plus de l'utilisation dans l'obtention de la moyenne durée, la date et l'heure (depuis une base commune date-heure) sont utilisés pour représenter la durée (une pratique courante), b), il est également utile pour obtenir un tableau de bord de type statistique sur ce la moyenne de la date est la date-heure de colonne d'une plage /d'un groupe de Lignes. c) Une norme (ou au moins devrait être la norme) de Requêtes ad-hoc pour surveiller et dépanner les valeurs dans une Colonne qui peut ne pas être valide jamais /plus /ou peut-être besoin d'être désapprouvé est de la liste, pour chaque valeur, le nombre d'événements et (si disponible) la Min, Avg et Max date-heure timbres associé à cette valeur.

Voici un exemple qui va vous montrer les différences dans la taille de stockage (octets) et la précision entre smalldatetime, datetime, datetime2(0), et datetime2(7):

DECLARE @temp TABLE (
    sdt smalldatetime,
    dt datetime,
    dt20 datetime2(0),
    dt27 datetime2(7)
)

INSERT @temp
SELECT getdate(),getdate(),getdate(),getdate()

SELECT sdt,DATALENGTH(sdt) as sdt_bytes,
    dt,DATALENGTH(dt) as dt_bytes,
    dt20,DATALENGTH(dt20) as dt20_bytes,
    dt27, DATALENGTH(dt27) as dt27_bytes FROM @temp

qui renvoie

sdt                  sdt_bytes  dt                       dt_bytes  dt20                 dt20_bytes  dt27                         dt27_bytes
2015-09-11 11:26:00  4          2015-09-11 11:25:42.417  8         2015-09-11 11:25:42  6           2015-09-11 11:25:42.4170000  8

Donc, si je veux stocker des informations à la seconde - mais pas à la milliseconde je peux enregistrer 2 octets si j'utilise datetime2(0) au lieu de datetime ou datetime2(7).

alors qu'il est augmenté de précision avec datetime2, certains clients ne prend pas en charge date, temps, ou datetime2 et vous forcer à se convertir à une chaîne littérale. Plus particulièrement Microsoft parle de "bas niveau" ODBC, OLE DB, JDBC, et SqlClient des problèmes avec ces types de données et a un graphique montrant comment chacun peut carte le type.

Si la valeur compatibilité plus de précision, utilisez datetime

Selon cet article, si vous souhaitez avoir la même précision de DateTime à l'aide de DateTime2 vous devez simplement utiliser DateTime2(3). Cela devrait vous donner la même précision, de prendre moins d'octets, et de fournir une gamme élargie.

Select ValidUntil + 1
from Documents

Le SQL ci-dessus ne fonctionne pas avec un DateTime2 champ.
Elle renvoie l'erreur "type d'Opérande clash: datetime2 est incompatible avec l'int"

Ajouter 1 pour obtenir le lendemain, c'est quelque chose que les développeurs ont été faites avec les dates pendant des années. Maintenant Microsoft ont une super nouvelle datetime2 champ qui ne peut pas gérer cette simple fonctionnalité.

"Nous allons utiliser ce nouveau type qui est pire que le vieux", je ne pense pas!