DateTime2 vs DateTime in SQL Server

Question

Which one     datetime datetime2   is the recommended way to store date and time in SQL Server 2008    I m aware of differences in precision  and storage space probably   but ignoring those for now  is there a best practice document on when to use what  or maybe we should just use datetime2 only

User · Answer

Interpretation of date strings into datetime and datetime2 can be different too  when using non-US DATEFORMAT settings  E g   set dateformat dmy declare  d datetime   d2 datetime2 select  d    2013-06-05    d2    2013-06-05  select  d   d2   This returns 2013-05-06  i e  May 6  for datetime  and 2013-06-05  i e  June 5  for datetime2   However  with dateformat set to mdy  both  d and  d2 return 2013-06-05   The datetime behavior seems at odds with the MSDN documentation of SET DATEFORMAT which states  Some character strings formats  for example ISO 8601  are interpreted independently of the DATEFORMAT setting   Obviously not true   Until I was bitten by this  I d always thought that yyyy-mm-dd dates would just be handled right  regardless of the language   locale settings

User · Answer

while there is increased precision with datetime2  some clients doesn t support date  time  or datetime2 and force you to convert to a string literal   Specifically Microsoft mentions  down level  ODBC  OLE DB  JDBC  and SqlClient issues with these data types and has a chart showing how each can map the type     If value compatability over precision  use datetime

User · Answer

Select ValidUntil   1 from Documents   The above SQL won t work with a DateTime2 field  It returns and error  Operand type clash  datetime2 is incompatible with int   Adding 1 to get the next day is something developers have been doing with dates for years  Now Microsoft have a super new datetime2 field that cannot handle this simple functionality    Let s use this new type that is worse than the old one   I don t think so

User · Answer

datetime2 wins in most aspects except  old apps Compatibility    larger range of values  better Accuracy smaller storage space   if optional user-specified precision is specified      please note the following points   Syntax   datetime2  fractional seconds precision   Look Below Storage Size    Precision  scale   0 to 7 digits  with an accuracy of 100ns   The default precision is 7 digits   Storage Size   6 bytes for precision less than 3   7 bytes for precision 3 and 4   All other precision require 8 bytes   DateTime2 3  have the same number of digits as DateTime but uses 7 bytes of storage instead of 8 byte  SQLHINTS- DateTime Vs DateTime2  Find more on datetime2 Transact-SQL MSDN article    image source   MCTS Self-Paced Training Kit  Exam 70-432   Microsoft   SQL Server   2008 - Implementation and Maintenance Chapter 3 Tables -  Lesson 1  Creating Tables -  page 66

User · Answer

According to this article  if you would like to have the same precision of DateTime using DateTime2 you simply have to use DateTime2 3    This should give you the same precision  take up one fewer bytes  and provide an expanded range

User · Answer

Almost all the Answers and Comments have been heavy on the Pros and light on the Cons   Here s a recap of all Pros and Cons so far plus some crucial Cons  in  2 below  I ve only seen mentioned once or not at all    PROS    1 1     More ISO compliant  ISO 8601   although I don   t know how this comes into play in practice    1 2     More range  1 1 0001 to 12 31 9999 vs  1 1 1753-12 31 9999   although the extra range  all prior to year 1753  will likely not be used except for ex   in historical  astronomical  geologic  etc  apps    1 3     Exactly matches the range of  NET   s DateTime Type   s range  although both convert back and forth with no special coding if values are within the target type   s range and precision except for Con   2 1 below else error   rounding will occur    1 4     More precision  100 nanosecond aka 0 000 000 1 sec  vs  3 33 millisecond aka 0 003 33 sec    although the extra precision will likely not be used except for ex   in engineering   scientific apps    1 5     When configured for similar  as in 1 millisec not  same   as in 3 33 millisec  as Iman Abidi has claimed  precision as DateTime  uses less space  7 vs  8 bytes   but then of course  you   d be losing the precision benefit which is likely one of the two  the other being range  most touted albeit likely unneeded benefits     CONS     2 1     When passing a Parameter to a  NET SqlCommand  you must specify System Data SqlDbType DateTime2 if you may be passing a value outside the SQL Server DateTime   s range and or precision  because it defaults to System Data SqlDbType DateTime   2 2     Cannot be implicitly   easily converted to a floating-point numeric    of days since min date-time  value to do the following to   with it in SQL Server expressions using numeric values and operators   2 2 1   add or subtract   of days or partial days   Note  Using DateAdd Function as a workaround is not trivial when you re needing to consider multiple if not all parts of the date-time   2 2 2   take the difference between two date-times for purposes of    age    calculation   Note  You cannot simply use SQL Server   s DateDiff Function instead  because it does not compute age as most people would expect in that if the two date-times happens to cross a calendar   clock date-time boundary of the units specified if even for a tiny fraction of that unit  it   ll return the difference as 1 of that unit vs  0   For example  the DateDiff in Day   s of two date-times only 1 millisecond apart will return 1 vs  0  days  if those date-times are on different calendar days  i e     1999-12-31 23 59 59 9999999    and    2000-01-01 00 00 00 0000000       The same 1 millisecond difference date-times if moved so that they don   t cross a calendar day  will return a    DateDiff    in Day   s of 0  days    2 2 3   take the Avg of date-times  in an Aggregate Query  by simply converting to    Float    first and then back again to DateTime   NOTE  To convert DateTime2 to a numeric  you have to do something like the following formula which still assumes your values are not less than the year 1970  which means you   re losing all of the extra range plus another 217 years   Note  You may not be able to simply adjust the formula to allow for extra range because you may run into numeric overflow issues   25567    DATEDIFF SECOND   d  1970-01-01     Time    DATEPART nanosecond   Time    1 0E   9    86400 0     Source      https   siderite dev blog how-to-translate-t-sql-datetime2-to html      Of course  you could also Cast to DateTime first  and if necessary back again to DateTime2   but you d lose the precision and range  all prior to year 1753  benefits of DateTime2 vs  DateTime which are prolly the 2 biggest and also at the same time prolly the 2 least likely needed which begs the question why use it when you lose the implicit   easy conversions to floating-point numeric    of days  for addition   subtraction    age   vs  DateDiff    Avg calcs benefit which is a big one in my experience   Btw  the Avg of date-times is  or at least should be  an important use case  a  Besides use in getting average duration when date-times  since a common base date-time  are used to represent duration  a common practice   b  it   s also useful to get a dashboard-type statistic on what the average date-time is in the date-time column of a range   group of Rows  c  A standard  or at least should be standard  ad-hoc Query to monitor   troubleshoot values in a Column that may not be valid ever   any longer and   or may need to be deprecated is to list for each value the occurrence count and  if available  the Min  Avg and Max date-time stamps associated with that value

User · Answer

I just stumbled across one more advantage for DATETIME2  it avoids a bug in the Python adodbapi module  which blows up if a standard library datetime value is passed which has non-zero microseconds for a DATETIME column but works fine if the column is defined as DATETIME2

User · Answer

The MSDN documentation for datetime recommends using datetime2  Here is their recommendation      Use the time  date  datetime2 and   datetimeoffset data types for new   work  These types align with the SQL   Standard  They are more portable    time  datetime2 and datetimeoffset   provide more seconds precision    datetimeoffset provides time zone   support for globally deployed   applications    datetime2 has larger date range  a larger default fractional precision  and optional user-specified precision  Also depending on the user-specified precision it may use less storage

User · Answer

Here is an example that will show you the differences in storage size  bytes  and precision between smalldatetime  datetime  datetime2 0   and 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  which returns 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  So if I want to store information down to the second - but not to the millisecond - I can save 2 bytes each if I use datetime2 0  instead of datetime or datetime2 7

User · Answer

I think DATETIME2 is the better way to store the date  because it has more efficiency than  the DATETIME  In SQL Server 2008 you can use DATETIME2  it stores a date and time  takes 6-8 bytes to store and has a precision of 100 nanoseconds  So anyone who needs greater time precision will want DATETIME2

User · Answer

Old Question    But I want to add something not already stated by anyone here     Note  This is my own observation  so don t ask for any reference   Datetime2 is faster when used in filter criteria   TLDR   In SQL 2016 I had a table with hundred thousand rows and a datetime column ENTRY TIME because it was required to store the exact time up to seconds  While executing a complex query with many joins and a sub query  when I used where clause as   WHERE ENTRY TIME  gt    2017-01-01 00 00 00  AND ENTRY TIME  lt   2018-01-01 00 00 00    The query was fine initially when there were hundreds of rows  but when number of rows increased  the query started to give this error   Execution Timeout Expired  The timeout period elapsed prior to completion of the operation or the server is not responding    I removed the where clause  and unexpectedly  the query was run in 1 sec  although now ALL rows for all dates were fetched  I run the inner query with where clause  and it took 85 seconds  and without where clause it took 0 01 secs   I came across many threads here for this issue as datetime filtering performance    I optimized query a bit  But the real speed I got was by changing the datetime column to datetime2   Now the same query that timed out previously takes less than a second   cheers

User · Answer

I concurr with  marc s and  Adam Poward -- DateTime2 is the preferred method moving forward   It has a wider range of dates  higher precision  and uses equal or less storage  depending on precision      One thing the discussion missed  however     Marc s states  Both types map to System DateTime in  NET - no difference there   This is correct  however  the inverse is not true   and it matters when doing date range searches  e g   find me all records modified on 5 5 2010       NET s version of  Datetime has similar range and precision to DateTime2   When mapping a  net Datetime down to the old  SQL DateTime an implicit rounding occurs    The old SQL DateTime is accurate to 3 milliseconds   This means that 11 59 59 997 is as close as you can get to the end of the day  Anything higher is rounded up to the following day   Try this    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    Avoiding this implicit rounding is a significant reason to move to DateTime2   Implicit rounding of dates clearly causes confusion    Strange datetime behavior in SQL Server http   bytes com topic sql-server answers 578416-weird-millisecond-part-datetime-data-sql-server-2000-a SQL Server 2008 and milliseconds http   improve dk archive 2011 06 16 getting-bit-by-datetime-rounding-or-why-235959-999-ltgt aspx http   milesquaretech com Blog post 2011 09 12 DateTime-vs-DateTime2-SQL-is-Rounding-My-999-Milliseconds  aspx

User · Answer

Accepted answer is great  just know that if you are sending a DateTime2 to the frontend - it gets rounded to the normal DateTime equivalent  This caused a problem for me because in a solution of mine I had to compare what was sent with what was on the database when resubmitted  and my simple comparison      didn t allow for rounding  So it had to be added

User · Answer

DateTime2 wreaks havoc if you are an Access developer trying to write Now   to the field in question  Just did an Access -  SQL 2008 R2 migration and it put all the datetime fields in as DateTime2  Appending a record with Now   as the value bombed out  It was okay on 1 1 2012 2 53 04 PM  but not on 1 10 2012 2 53 04 PM    Once character made the difference  Hope it helps somebody

User · Answer

DATETIME2 has a date range of  0001   01   01  through  9999   12   31  while the DATETIME type only supports year 1753-9999   Also  if you need to  DATETIME2 can be more precise in terms of time  DATETIME is limited to 3 1 3 milliseconds  while DATETIME2 can be accurate down to 100ns   Both types map to System DateTime in  NET - no difference there   If you have the choice  I would recommend using DATETIME2 whenever possible  I don t see any benefits using DATETIME  except for backward compatibility  - you ll have less trouble  with dates being out of range and hassle like that    Plus  if you only need the date  without time part   use DATE - it s just as good as DATETIME2 and saves you space  too   -  Same goes for time only - use TIME  That s what these types are there for

[sql] DateTime2 vs DateTime in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to datetime

Examples related to datetime2