Question DateTime2 vs DateTime dans SQL Server


Laquelle:

est la méthode recommandée pour stocker la date et l'heure dans SQL Server 2008+?

Je suis conscient des différences de précision (et de l'espace de stockage probablement), mais en ignorant ceux pour l'instant, y a-t-il un document de meilleure pratique sur quand utiliser quoi, ou peut-être que nous devrions simplement utiliser datetime2 seulement?


620
2017-08-26 11:45


origine


Réponses:


La documentation MSDN pour datetime recommande d'utiliser datetime2. Voici leur recommandation:

Utilisez le time, date, datetime2 et    datetimeoffset types de données pour nouveau   travail. Ces types s'alignent avec le SQL   La norme. Ils sont plus portables.    time, datetime2 et datetimeoffset   fournir plus de secondes de précision.    datetimeoffset fournit le fuseau horaire   soutien aux déploiements mondiaux   applications.

datetime2 a une plage de dates plus longue, une précision fractionnelle par défaut plus grande et une précision facultative spécifiée par l'utilisateur. En outre, en fonction de la précision spécifiée par l'utilisateur, il peut utiliser moins de stockage.


513
2017-12-10 21:17



DATETIME2 a une plage de dates de "0001/01/01" à "9999/12/31" tandis DATETIME type ne prend en charge que l'année 1753-9999.

Aussi, si vous en avez besoin, DATETIME2 peut être plus précis en termes de temps; DATETIME est limité à 3 1/3 millisecondes, tandis DATETIME2 peut être précis jusqu'à 100ns.

Les deux types correspondent à System.DateTime dans .NET - pas de différence là-bas.

Si vous avez le choix, je recommanderais d'utiliser DATETIME2 dès que possible. Je ne vois aucun avantage à utiliser DATETIME (sauf pour la rétrocompatibilité) - vous aurez moins de problèmes (avec des dates hors de portée et des tracas comme ça).

Plus: si vous avez seulement besoin de la date (sans partie de temps), utilisez DATE - c'est aussi bon que DATETIME2 et vous sauve aussi de l'espace! :-) Idem pour le temps seulement - utilisation TIME. Voilà ce que ces types sont là pour!


425
2017-08-26 11:56



datetime2 gagne dans la plupart des aspects sauf (compatibilité avec les anciennes applications)

  1. plus grand gamme de valeurs 
  2. meilleur Précision
  3. plus petit espace de stockage  (si la précision facultative spécifiée par l'utilisateur est spécifiée)

SQL Date and time data types compare - datetime,datetime2,date,TIME

s'il vous plaît noter les points suivants

  • Syntaxe
    • datetime2 [(fractional secondes precision => Regardez sous la taille de stockage)]
  • Précision, échelle
    • 0 à 7 chiffres, avec une précision de 100ns.
    • La précision par défaut est de 7 chiffres.
  • Taille de stockage
    • 6 octets pour une précision inférieure à 3;
    • 7 octets pour la précision 3 et 4.
    • Toute autre précision nécessite 8 octets.
  • DateTime2 (3) avoir le même nombre de chiffres que DateTime mais utilise 7 octets de stockage au lieu de 8 octets (SQLHINTS - DateTime Vs DateTime2)
  • Trouver plus sur datetime2 (article MSDN Transact-SQL)

source de l'image: Kit de formation auto-rythmé MCTS (Examen 70-432): Microsoft SQL Server 2008 - Mise en œuvre et maintenance Chapitre 3: Tableaux -> Leçon 1: Créer des tableaux -> page 66


152
2017-09-11 06:52



Je suis d'accord avec @marc_s et @Adam_Poward - DateTime2 est la méthode préférée pour aller de l'avant. Il a une plus grande gamme de dates, une plus grande précision et utilise un stockage égal ou inférieur (en fonction de la précision).

Une chose que la discussion a manqué, cependant ...
Etats @Marc_s: Both types map to System.DateTime in .NET - no difference there. C'est correct, Cependant, l'inverse n'est pas vrai... et il est important de faire des recherches sur les plages de dates (par exemple, "retrouvez-moi tous les enregistrements modifiés le 5/5/2010").

La version de .NET de Datetime a une gamme et une précision similaires à DateTime2. Lors de la mise en correspondance d'un .net Datetime jusqu'à l'ancien SQL DateTime un l'arrondissement implicite se produit. L'ancien SQL DateTime est précis à 3 millisecondes. Cela signifie que 11:59:59.997 est aussi proche que possible de la fin de la journée. Tout ce qui est plus élevé est arrondi au jour suivant.

Essaye ça :

declare @d1 datetime   = '5/5/2010 23:59:59.999'
declare @d2 datetime2  = '5/5/2010 23:59:59.999'
declare @d3 datetime   = '5/5/2010 23:59:59.997'
select @d1 as 'IAmMay6BecauseOfRounding', @d2 'May5', @d3 'StillMay5Because2msEarlier'

Éviter cet arrondi implicite est une raison importante pour passer à DateTime2. L'arrondissement implicite des dates entraîne clairement une confusion:


101
2018-03-08 18:00



DateTime2 fait des ravages si vous êtes un développeur Access essayant d'écrire Now () dans le champ en question. Juste fait une migration d'Access -> SQL 2008 R2 et il a mis tous les champs de date-heure dedans comme DateTime2. Ajout d'un enregistrement avec Now () comme valeur balisée. C'était correct le 1/1/2012 14:53:04 PM, mais pas le 10/01/2012 14:53:04.

Une fois le personnage a fait la différence. J'espère que ça aide quelqu'un.


13
2018-02-13 19:52



Voici un exemple qui vous montrera les différences de taille de stockage (octets) et de 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 revient

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 chacun si j'utilise datetime2 (0) au lieu de datetime ou datetime2 (7).


12
2017-09-11 17:29



Presque toutes les réponses et les commentaires ont été lourds sur les avantages et la lumière sur les inconvénients. Voici un récapitulatif de tous les avantages et inconvénients jusqu'à présent, plus quelques inconvénients cruciaux (dans # 2 ci-dessous) que j'ai seulement vu mentionné une fois ou pas du tout.

  1. AVANTAGES:

1.1. Plus conforme ISO (ISO 8601) (bien que je ne sache pas comment cela entre en pratique).

1.2. Plus de portée (1/1/0001 à 31/12/9999 contre 1/1/1775/12/31/9999) (bien que la plage supplémentaire, toutes antérieures à l'année 1753, ne sera probablement pas utilisée sauf pour ex., dans les applications historiques, astronomiques, géologiques, etc.).

1.3. Correspond exactement à la plage de .NET DateTime La plage du type (bien que les deux convertissent d'avant en arrière sans codage spécial si les valeurs sont dans la plage et la précision du type de cible, sauf pour Con # 2.1 ci-dessous, sinon erreur / arrondi se produira).

1.4. Plus de précision (100 nanosecondes aka 0,000,000,1 sec contre 3,33 millisecondes aka 0,003,33 sec.) (Bien que la précision supplémentaire ne sera probablement pas utilisée sauf pour ex., Dans les applications d'ingénierie / scientifiques).

1.5. Lorsqu'il est configuré pour similaire (comme dans 1 millisec pas "même" (comme dans 3,33 millisec) comme Iman Abidi a affirmé) précision DateTime, utilise moins d'espace (7 contre 8 octets), mais bien sûr, vous perdriez le bénéfice de précision qui est probablement l'un des deux avantages les plus inutiles.

  1. LES INCONVÉNIENTS:

2.1. Lors du passage d'un paramètre à un .NET SqlCommand, vous devez spécifier System.Data.SqlDbType.DateTime2 si vous transmettez une valeur en dehors de SQL Server DateTimeLa portée et / ou la précision, car il est par défaut System.Data.SqlDbType.DateTime.

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

2.2.1. ajouter ou soustraire # de jours ou de jours partiels. Remarque: Utilisation DateAdd La fonction de solution de contournement n'est pas triviale lorsque vous devez prendre en compte plusieurs parties sinon la totalité de la date et de l'heure.

2.2.2. faire la différence entre deux dates-heures aux fins du calcul de «l'âge». Remarque: Vous ne pouvez pas utiliser simplement SQL Server DateDiff Fonctionnez plutôt, car il ne calcule pas age comme la plupart des gens s'attendent à ce que si les deux dates-dates traversent une limite date / heure du calendrier / horloge des unités spécifiées si même pour une infime fraction de cette unité, il retournera la différence comme 1 de cette unité vs 0. Par exemple, le DateDiff dans Dayde deux dates seulement à 1 milliseconde d'intervalle retournera 1 contre 0 (jours) si ces dates sont sur des jours calendaires différents (par exemple, "1999-12-31 23: 59: 59.9999999" et "2000-01- 01 00: 00: 00.0000000 "). Les mêmes dates-heures de différence de 1 milliseconde, si elles sont déplacées de sorte qu'elles ne traversent pas un jour calendaire, renverront un "DateDiff" dans DayEst de 0 (jours).

2.2.3. prendre la Avg date-fois (dans une requête d'agrégation) en convertissant simplement en "Float" d'abord, puis de nouveau à DateTime.

NOTE: Pour convertir DateTime2 à un chiffre, vous devez faire quelque chose comme la formule suivante qui suppose encore que vos valeurs ne sont pas inférieures à l'année 1970 (ce qui signifie que vous perdez toute la gamme supplémentaire plus 217 ans.) Note: Vous ne pourrez peut-être pas il suffit d'ajuster la formule pour autoriser une plage supplémentaire, car vous risquez de rencontrer des problèmes de débordement numérique.

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

Bien sûr, vous pourriez aussi Cast à DateTime d'abord (et si nécessaire de nouveau à DateTime2), mais vous perdriez la précision et la gamme (tous avant 1753) des avantages de DateTime2 contre. DateTime qui sont prolly les 2 plus grands et en même temps prolly le 2 moins probablement nécessaire qui pose la question pourquoi l'utiliser quand vous perdez les conversions implicites / faciles au nombre à virgule flottante numérique (nombre de jours) pour l'addition / soustraction / "l'âge " (contre. DateDiff) / Avg calcs avantage qui est un gros dans mon expérience.

Btw, le Avg des dates-heures est (ou au moins devrait be) un cas d'utilisation important. a) Outre l'utilisation pour obtenir la durée moyenne lorsque des dates-heures (depuis une date-base commune) sont utilisées pour représenter la durée (une pratique courante), b) il est également utile d'obtenir une statistique de type tableau de bord sur heure est dans la colonne date-heure d'une plage / groupe de lignes. c) Une norme (ou au moins devrait être standard) Requête ad-hoc pour surveiller / dépanner des valeurs dans une colonne qui ne sont pas toujours valides et / ou qui peuvent avoir besoin d'être déconseillées est de lister pour chaque valeur le nombre d'occurrences et (si disponible) le Min, Avg et Max les horodatages associés à cette valeur.


8
2017-07-10 19:00