DateTime2 vs DateTime de SQL Server
Lequel:
est la recommandé de 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 sans doute), mais en ignorant ceux qui pour l'instant, est-il un document de pratiques exemplaires sur quand utiliser quoi, ou peut-être que nous devrions simplement utiliser datetime2
seulement?
Vous devez vous connecter pour publier un commentaire.
La documentation MSDN pour datetime recommande l'utilisation de datetime2. Voici leur recommandation:
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
Type (ou tout autre Type SQL Server pour cette question)? Voir le Contre dans mon 7/10/17 Réponse ci-dessous pour pourquoi je pose la question.DATETIME2
a une plage de dates "0001 /01 /01" à travers "9999 /12 /31" alors que leDATETIME
uniquement pour le type prend en charge 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 queDATETIME2
peut être exacte à 100ns.Les deux types de carte de
System.DateTime
dans .NET - pas de différence.Si vous avez le choix, je vous conseille d'utiliser
DATETIME2
chaque fois que possible. Je ne vois pas d'avantages à l'aide desDATETIME
(sauf pour des raisons de compatibilité ascendante) - vous aurez moins de problèmes (avec les dates d'être hors de la plage et de tracas comme ça).Plus: si vous avez uniquement besoin de la date (sans la partie du temps), l'utilisation de la DATE - c'est tout aussi bon que
DATETIME2
et vous permet d'économiser de l'espace, trop! 🙂 En va de même pour le temps seulement - utilisationTIME
. C'est ce que ces types sont là pour!DATETIME2
DateTime
struct NULL .....Nullable<DateTime>
est-ce?DateTime
vsDateTime2
. Voir la Contre partie de ma Réponse datée 7/10/17 ci-dessous.datetime2 victoires dans la plupart des aspects à l'exception des (anciennes applications de Compatibilité)
veuillez noter les points suivants
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
datetime2
est génial(Gagnant)datetime2
utiliser moins d'espace de stockage quedatetime
et encore offrir une plus grande portée et une plus grande précision?Je concurr avec @marc_s et @Adam_Poward -- DateTime2 est la méthode préférée pour aller de l'avant. Il dispose d'un large éventail de dates, une plus grande précision, et utilise l'égalité ou moins d'espace de stockage (en fonction de la précision).
Une chose à la discussion oubliée, mais...
@Marc_s états:
Both types map to System.DateTime in .NET - no difference there
. C'est correct, cependant, l'inverse n'est pas vrai...et c'est important quand on fait de la plage de dates des recherches (par exemple, "trouvez-moi tous les enregistrements modifiés sur 5/5/2010")..NET version de
Datetime
similaires à la gamme et la précision deDateTime2
. Lors du mappage d'une .netDatetime
vers le bas à l'ancien SQLDateTime
un implicite arrondi. L'ancien SQLDateTime
est précis à 3 millisecondes. Cela signifie que11:59:59.997
est aussi proche que vous pouvez obtenir à la fin de la journée. Quelque chose de plus élevé est arrondi au lendemain.Essayez ceci :
Éviter cet implicite de l'arrondissement est une raison importante pour passer à DateTime2. Implicite de l'arrondissement des dates clairement les causes de la confusion:
20100505
à5/5/2010
? L'ancien format fonctionnera avec n'importe quelle région dans SQL Server. Ce dernier va casser:SET LANGUAGE French; SELECT Convert(datetime, '1/7/2015')
oops:2015-07-01 00:00:00.000
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.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).2.1. Lors du passage d'un Paramètre à un .NET
SqlCommand
, vous devez spécifierSystem.Data.SqlDbType.DateTime2
si vous pouvez peut-être le passage d'une valeur à l'extérieur de SQL ServerDateTime
’de la gamme s et/ou de précision, car la valeur par défautSystem.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 pasage
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, leDateDiff
dansDay
’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” dansDay
’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 deDateTime2
vsDateTime
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" (vsDateDiff
) /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) laMin
,Avg
etMax
date-heure timbres associé à cette valeur.DateTime
), sont tous liés à des effets sur les Requêtes SQL Server et les déclarations.DateTime2 fait des ravages si vous êtes un Accès développeur tente d'écrire Maintenant() pour le domaine en question. Juste fait un Access -> SQL 2008 R2 migration et il a mis tous les champs de datetime comme DateTime2. L'ajout d'un enregistrement avec Now() comme valeur bombardé. Il a été d'accord sur le 1/1/2012, 2:53:04 PM, mais pas sur 1/10/2012 2:53:04 PM.
Une fois le personnage a fait la différence. Espérons que cela aide quelqu'un.
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):
qui renvoie
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).
Interprétation de la date de chaînes en
datetime
etdatetime2
peut être différent aussi, lors de l'utilisation de la non-USDATEFORMAT
paramètres. E. g.Cela renvoie
2013-05-06
(c'est à dire le 6 Mai) pourdatetime
, et2013-06-05
(c'est à dire 5 juin) pourdatetime2
. Cependant, avecdateformat
ensemble demdy
, à la fois@d
et@d2
retour2013-06-05
.La
datetime
comportement semble en contradiction avec la La documentation MSDN deSET 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 j'ai été mordu par la présente, j'ai toujours pensé que
yyyy-mm-dd
dates voudrais juste être manipulé droit, indépendamment de la langue et les paramètres régionaux.SET LANGUAGE FRENCH; DECLARE @d DATETIME = '20130605'; SELECT @d;
Essayer de nouveau avec les tirets.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
Vieille Question... Mais je tiens à ajouter quelque chose de pas déjà dit par quelqu'un d'ici... (Note: Ceci est ma propre observation, afin de ne pas demander de toute référence)
Datetime2 est plus rapide lorsque utilisé dans les critères de filtre.
TLDR:
Dans SQL 2016 j'avais un tableau avec des centaines de milliers de lignes et une colonne datetime ENTRY_TIME parce que c'était nécessaire pour stocker l'heure exacte à la seconde près. 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:
La requête était bien au début quand il y avait des centaines de lignes, mais lorsque le nombre de lignes a augmenté, la requête a commencé à donner cette erreur:
J'ai supprimé la clause where, et de façon inattendue, la requête a été exécutée en 1 sec, bien que maintenant TOUTES les lignes pour toutes les dates ont été extraits. Je lance l'intérieur de la requête avec la clause where, et il a fallu 85 secondes, et sans clause where, il a pris de 0,01 secondes.
Je suis tombé sur de nombreux threads ici pour ce problème comme datetime les performances du filtrage
J'ai optimisé requête un peu. Mais la vitesse réelle que j'ai eu était en changeant la colonne datetime à datetime2.
Maintenant la même requête a expiré précédemment prend moins d'une seconde.
acclamations
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.
J'ai juste trébuché à travers un avantage de plus pour
DATETIME2
: il évite un bug dans le script Pythonadodbapi
module, qui explose si une bibliothèque standarddatetime
valeur est passée qui a non-zero microsecondes pour uneDATETIME
colonne, mais fonctionne très bien si la colonne est définie commeDATETIME2
.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!
datetime
etdatetime2
types de données ont été introduites dans SQL Server 2008. Vous bénéficiez également deOperand type clash: date is incompatible with int
de ladate
type qui a été autour depuis le jour de la dot. Tous les trois types de données fonctionnent très bien avecdateadd(dd, 1, ...)
bien.Je pense que
DATETIME2
est la meilleure façon de stocker lesdate
, parce qu'il a plus d'efficacité quele
DATETIME
. DansSQL Server 2008
vous pouvez utiliserDATETIME2
, il enregistre la date et l'heure, prend de 6 à 8bytes
pour stocker et a une précision de100 nanoseconds
. Donc quelqu'un qui a besoin de plus de temps de précision voulezDATETIME2
.