DateTime2 vs DateTime in SQL Server

lequel:

est le recommandé de stocker la date et l'heure dans SQL Server 2008+?

je suis conscient des différences dans la précision (et l'espace de stockage probablement), mais en ignorant ceux pour l'instant, est-il un document de meilleure pratique sur quand utiliser quoi, ou peut-être nous devrait - on utiliser datetime2 seulement?

635
demandé sur abatishchev 2009-08-26 15:45:10

14 réponses

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

utilisez la time , date , datetime2 et datetimeoffset types de données pour les nouveaux travail. Ces types s'alignent avec le SQL Norme. Ils sont plus mobiles. time , datetime2 et datetimeoffset fournir plus de précision en secondes. datetimeoffset fournit le fuseau horaire soutien pour déployée à l'échelle mondiale application.

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

527
répondu Adam Porad 2018-07-13 19:20:56

DATETIME2 a une plage de dates de "0001 / 01 / 01" par "9999 / 12 / 31 "tandis que le type DATETIME soutient seulement l'année 1753-9999.

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

les deux types de carte à System.DateTime en .NET - aucune différence.

si vous avez le choix, je il est recommandé d'utiliser DATETIME2 dans la mesure du possible. Je ne vois aucun avantage à utiliser DATETIME (sauf pour la rétrocompatibilité) - vous aurez moins de problèmes (avec les dates étant hors de portée et tracas comme cela).

Plus: si vous n'avez besoin que de la date (sans temps partiel), utilisez la DATE - c'est aussi bien que DATETIME2 et vous économise de l'espace, aussi! :- ) Même chose pour le temps seulement-utilisez TIME . C'est pour ça que ces types sont là!

435
répondu marc_s 2016-01-06 08:25:33

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

  1. plus grand plage de valeurs
  2. meilleure précision
  3. plus petit espace de stockage (si une 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 note les points suivants

  • syntaxe
    • datetime2[(fractions de secondes de précision=> Regardez en Dessous de 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) ont le même nombre de chiffres que DateTime mais utilise 7 octets de stockage au lieu de 8 octets ( SQLHINTS - DateTime Vs DateTime2 )
  • pour en savoir plus sur datetime2(article MSDN de Transact-SQL)

source de l'image : MCTS Self-Paced Training Kit (Exam 70-432): Microsoft® SQL Server® 2008-Implementation and Maintenance Chapitre 3: Tables - > Leçon 1: Création de Tables - > page 66

161
répondu Iman Abidi 2017-10-18 21:14:10

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 plage de dates, une plus grande précision, et utilise le stockage égal ou moins (selon la précision).

une chose que la discussion a toutefois manquée...

@Marc_s déclare: Both types map to System.DateTime in .NET - no difference there . C'est exact, cependant, l'inverse n'est pas vrai ...et c'est important quand on fait une plage de dates recherches (par exemple "me trouver tous les enregistrements modifiés le 5/5/2010").

. la version de Datetime de Net a une portée et une précision similaires à DateTime2 . Lors de la cartographie d'un .net Datetime vers le bas à L'ancien SQL DateTime an arrondissement implicite se produit . Le vieux SQL DateTime est précis à 3 millisecondes. Cela signifie que 11:59:59.997 est aussi proche que vous pouvez obtenir à la fin de la journée. Tout montant plus élevé est arrondi au jour suivant.

essayez ceci:

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 arrondissement implicite est une raison importante pour passer à DateTime2. L'arrondissement implicite des dates est source de confusion:

101
répondu EBarr 2017-05-23 12:02:46

DateTime2 fait des ravages si vous êtes un développeur D'accès essayant D'écrire maintenant() sur le champ en question. Je viens de faire une migration Access -> SQL 2008 R2 et il a mis tous les champs datetime en as DateTime2. Ajouter un enregistrement avec Now() alors que la valeur s'est envolée. Il était d'accord sur 1/1/2012 14:53:04 PM, mais pas sur 1/10/2012 14:53:04 PM.

une fois le caractère fait la différence. Espérons que cela aide quelqu'un.

13
répondu Rhett A Brown 2012-02-13 19:52:43

voici un exemple qui vous montrera les différences de taille de stockage (bytes) 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 retourne

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).

12
répondu Baodad 2015-10-26 14:39:38

presque toutes les réponses et les commentaires ont été lourds sur le pour et léger sur le contre. Voici un résumé de tous les avantages et les inconvénients jusqu'à présent plus quelques inconvénients cruciaux (au #2 ci-dessous) j'ai seulement vu mentionné une fois ou pas du tout.

  1. PROS:

1.1. Plus conforme à L'ISO (ISO 8601) (bien que je ne sache pas comment cela entre en jeu dans la pratique).

1.2. Plus de portée (1/1/0001 à 12/31/9999 vs. 1/1/1753-12/31/9999) (bien que la fourchette supplémentaire, toutes antérieures à l'année 1753, ne sera probablement pas utilisée, sauf dans le cas de la catégorie ex. historiques, astronomiques, géologiques, etc. apps).

1.3. Correspond exactement à la plage de valeurs du Type .net DateTime (bien que les deux convertissent en va-et-vient sans codage spécial si les valeurs se situent dans la plage et la précision du type cible sauf pour Con # 2.1 ci-dessous sinon erreur / arrondissement se produira).

1.4. Plus de précision (100 nanosecondes soit 0.000, 000, 1 sec. vs. 3.33 milliseconde aka 0.003, 33 sec.) (bien que la précision supplémentaire ne sera probablement pas utilisée, sauf dans le cas de la, en ingénierie / applications scientifiques).

1.5. Lorsque configuré pour similaire (comme dans 1 millisec pas "même" (comme dans 3.33 millisec) comme Iman Abidi a déclaré) précision comme DateTime , utilise moins d'Espace (7 vs 8 octets), mais alors, bien sûr, vous perdriez l'avantage de précision qui est probablement l'un des deux (l'autre étant la fourchette) sont les avantages les plus vantés, bien qu'ils soient probablement inutiles.

  1. CONS:

2.1. Lorsque vous passez un paramètre à un .NET SqlCommand , vous devez spécifier System.Data.SqlDbType.DateTime2 si vous passez une valeur en dehors de la plage et/ou de la précision du serveur SQL DateTime , car il est par défaut à System.Data.SqlDbType.DateTime .

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

2.2.1. additionnez ou soustrayez le nombre de jours ou de parties de jours. Remarque: L'utilisation de la fonction DateAdd comme solution de contournement n'est pas anodine lorsque vous devez considérer plusieurs sinon toutes les parties de la date-heure.

2.2.2. prenez la différence entre deux dates-heures aux fins du calcul de l ' "âge". Note: vous ne pouvez pas utiliser simplement SQL La fonction DateDiff du serveur à la place, parce qu'il ne calcule pas age comme la plupart des gens s'y attendrait dans ce que si les deux dates-heures arrive à traverser une date Calendrier / horloge limite de l'heure 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é par rapport à 0. Par exemple, le DateDiff dans Day de deux datations séparées d'une milliseconde seulement retournera 1 vs 0 (jours) si ces datations sont à des jours civils différents (c.-à-d. "1999-12-31 23:59: 59.9999999" et "2000-01-01 00:00:00.0000000"). La même différence de 1 milliseconde date-heure s'ils sont déplacés de sorte qu'ils ne traversent pas un jour civil, retournera un "DateDiff" dans Day ’s de 0 (jours).

2.2.3. prenez le Avg des dates-heures (dans une requête agrégée) en convertissant simplement en" Float "d'abord, puis de nouveau en DateTime .

NOTE: pour convertir DateTime2 en numérique, vous devez faire quelque chose comme la formule suivante qui suppose toujours vos valeurs ne sont pas moins que l'année 1970 (ce qui signifie que vous perdez toute la gamme Supplémentaire plus 217 autres années. REMARQUE: Vous ne pouvez peut-être pas simplement ajuster la formule pour tenir compte de la marge supplémentaire, car vous pourriez 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 - Source: " https://siderite.blogspot.com/2015/08/how-to-translate-t-sql-datetime2-to.html "

de 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 portée (tous avant l'année 1753) des avantages de DateTime2 vs. DateTime qui sont prolly les 2 plus grands et aussi en même temps prolly les 2 moins nécessaires qui amène la question pourquoi l'utiliser quand vous perdez les conversions implicites / faciles à flotter point numérique (# de jours) pour addition / soustraction / "âge" (vs. DateDiff ) / Avg calcs benefit qui est un gros dans mon expérience.

Btw, le Avg de date-heures est (ou au moins devrait être) un cas d'utilisation importante. a) en plus de l'utilisation pour obtenir la durée moyenne quand date-heures (depuis une date de base commune-heure) sont utilisés pour représenter la durée (une pratique courante), b) il est également utile d'obtenir une statistique de type tableau de bord sur ce que la date-heure moyenne est dans la colonne date-heure d'une gamme / groupe de rangées. c) Une norme (ou au moins devrait devrait être standard) requête ad hoc pour surveiller / dépanner les valeurs dans une colonne qui peut ne pas être valide jamais / plus et / ou peut avoir besoin d'être déprécié est d'énumérer pour chaque valeur le nombre d'occurrence et (si disponible) le Min , Avg et Max date-heure timbres associés à cette valeur.

10
répondu Tom 2017-08-10 23:36:00

interprétation des chaînes de date en datetime et datetime2 peut être différente aussi, en utilisant des paramètres non-US DATEFORMAT . Par exemple:

set dateformat dmy
declare @d datetime, @d2 datetime2
select @d = '2013-06-05', @d2 = '2013-06-05'
select @d, @d2

il retourne 2013-05-06 (c.-à-d. le 6 mai) pour datetime , et 2013-06-05 (c.-à-d. le 5 juin) pour datetime2 . Toutefois, avec dateformat mis à mdy , à la fois @d et @d2 retour 2013-06-05 .

le comportement datetime semble en contradiction avec le documentation MSDN de SET DATEFORMAT qui stipule: des chaînes de caractères, les formats, par exemple la norme ISO 8601, sont interprétées de manière indépendante du paramètre DATEFORMAT . Évidemment pas vrai!

Jusqu'à ce que je sois mordu par ceci, j'ai toujours pensé que les dates yyyy-mm-dd seraient juste gérées correctement, indépendamment des paramètres de langue / locale.

6
répondu Richard Fawcett 2013-06-21 13:11:19

alors qu'il y a une augmentation de précision avec datetime2 , certains clients ne supporte pas date , time , ou datetime2 et vous forcer à convertir en chaîne littérale. Spécifiquement Microsoft mentionne" down level " ODBC, OLE DB, JDBC, et les problèmes SqlClient avec ces types de données et a un graphique montrant comment chacun peut mapper le type.

valeur compatibilité plus de précision, utilisez datetime

6
répondu FistOfFury 2014-06-25 20:56:15

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, prendre un byte de moins, et fournir une gamme étendue.

3
répondu jKlaus 2014-08-18 18:06:41

je viens de tomber sur un avantage supplémentaire pour DATETIME2 : il évite un bug dans le module Python adodbapi , qui explose si une bibliothèque standard datetime valeur est passée qui a non-zéro microsecondes pour une DATETIME colonne, mais fonctionne très bien si la colonne est définie comme DATETIME2 .

2
répondu Bob Kline 2017-11-04 15:31:26

Vieille Question... Mais je voudrais ajouter quelque chose qui n'a déjà été dit par personne ici... (Note: Ceci est ma propre observation, alors ne demandez aucune référence)

Datetime2 est plus rapide lorsqu'il est utilisé dans les critères de filtre.

TLDR:

dans SQL 2016 j'ai eu une table avec cent mille lignes et une date de colonne ENTRY_TIME parce qu'il était nécessaire de stocker le temps exact jusqu'à deuxième. Lors de l'exécution d'une requête complexe avec de nombreuses jointures et sous-requêtes, lorsque j'ai utilisé la clause where comme:

WHERE ENTRY_TIME >= '2017-01-01 00:00:00' AND ENTRY_TIME < '2018-01-01 00:00:00'

la requête était bonne au départ quand il y avait des centaines de lignes, mais quand le nombre de lignes a augmenté, la requête a commencé à donner cette erreur:

Execution Timeout Expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.

j'ai supprimé la clause where, et de façon inattendue, la requête a été exécutée en 1 seconde, bien que maintenant toutes les lignes pour toutes les dates ont été récupérées. J'exécute la requête interne avec la clause où, et il ça a pris 85 secondes, et sans la clause d'où ça a pris 0,01 secondes.

je suis tombé sur de nombreux fils ici pour ce numéro comme datetime performance de filtrage

j'ai un peu optimisé la requête. Mais la vitesse réelle que j'ai obtenue était en changeant la colonne datetime en datetime2.

maintenant la même requête que timed out prend moins d'une seconde.

cheers

1
répondu Khan 2018-02-09 05:08:27
Select ValidUntil + 1
from Documents

le SQL ci-dessus ne fonctionnera pas avec un champ DateTime2. Il renvoie et error "Operand type clash: datetime2 est incompatible avec int "

ajouter 1 pour obtenir le lendemain est quelque chose que les développeurs ont fait avec des dates depuis des années. Maintenant Microsoft a un champ datetime2 super nouveau qui ne peut pas gérer cette fonctionnalité simple.

"utilisons ce nouveau type qui est pire que l'ancien", Je ne pense pas!

0
répondu Paul McCarthy 2017-05-25 09:17:38

je pense que DATETIME2 est le meilleur moyen de stocker la date, parce qu'il a plus d'efficacité que le type DATETIME. Dans SQL Server 2008, vous pouvez utiliser DATETIME2, il stocke une date et une heure, prend 6-8 octets à stocker et a une précision de 100 nanosecondes. Ainsi, toute personne qui a besoin d'une plus grande précision de temps voudra DATETIME2.

-1
répondu James 2013-11-20 09:06:54