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?

Solution 1

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.

Solution 2

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!

Solution 3

datetime2 wins in most aspects except (old apps Compatibility)

1. larger range of values
2. better Accuracy
3. 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

Solution 4

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

Solution 5

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.

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

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

View more solutions

Comments

[BBlake]

I would agree with Marc. Unless you need backwards compatibility for some reason, always go with DATETIME2. Storage space is cheap and according to what I've seen in my own experience and read elsewhere, there is no difference in processing overhead between the two.

[Triynko]

Be careful when adding a .NET DateTime value as a parameter to an SqlCommand, because it likes to assume it's the old datetime type, and you'll get an error if you try to write a DateTime value that's outside that 1753-9999 year range unless you explicitly specify the type as System.Data.SqlDbType.DateTime2 for the SqlParameter. Anyway, datetime2 is great, because it can store any value that can be stored in the .NET DateTime type.

[user3001801]

@marc_s - Don't understand your point about preferring DATETIME2 when all else is equal. Is the flexibility to support year 1-1752 really a plus for most applications? I've never had an app that represented a year earlier than the late 1800's and it seems like it would encourage accidents like mis-coding 2011 as 11.

[marc_s]

@JohnFx: I've had numerous occasions where apps would represent a "unknown" date as 01/01/0001 - which always causes trouble on SQL Server, unless you can use DATETIME2

[user3001801]

@marc_s - Isn't that what null is for?

[piers7]

@Marc: There is a difference in the mapping to .Net types: DateTime2 is isomorphic with the .Net DateTime type, so no 'out of range' errors and probably some really tiny performance improvement as no conversion required.

[Adam Tuliper]

@JohnFX - a bit late here - but you wouldn't set a datetime to null. you would use Nullable<datetime> or datetime? which handles null just fine - and in mapping to a proc would simply do param.value = someDateTime?? DBValue.Null Its unfortunate we're stuck with a datatype with a number after it - just seems so 'generic' : )

[Triynko]

Lol, I just tried to upvote my own comment (above), before I realized it was my own comment (made over a year ago). I'm still dealing with the .NET framework's dumb design decision to TRUNCATE all DateTime values by default when passed as SqlParameters unless you explicitly set it to the more precise SqlDbType.DateTime2. So much for automatically inferring the correct type. Really, they should have made the change transparent, replacing the less precise, less efficient, limited-range implementation, and kept the original "datetime" type name. See also stackoverflow.com/q/8421332/88409

[Adir D]

You can also avoid this rounding by not trying to find the "end" of a day anyway. >= May 5 AND < May 6 is much safer and will work on any of the date/time types (except TIME of course). Also suggest avoiding regional, ambiguous formats like m/d/yyyy.

[Adir D]

Nope. For ISO 8601 I think you meant YYYYMMDD (no dashes). SET LANGUAGE FRENCH; DECLARE @d DATETIME = '20130605'; SELECT @d; Try again with the dashes.

[Richard Fawcett]

The standard allows for both YYYY-MM-DD and YYYYMMDD formats for calendar date representations. I think MSDN should be more specific about which subset of the ISO 8601 specification is interpreted independently!

[Adir D]

I know that but in SQL Server only the no-dash syntax is safe.

[EBarr]

@AaronBertrand - totally agree, but looking at the number of questions we have the matter it seemed worth describing.

[FistOfFury]

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. If you're concerned more about compatability than precision, use datetime

[ChrisW]

@marc_s Isn't that what Nullable<DateTime> is for?

[ErikE]

Why did you switch from 20100505 to 5/5/2010? The former format will work with any region in SQL Server. The latter will break: SET LANGUAGE French; SELECT Convert(datetime, '1/7/2015') oops: 2015-07-01 00:00:00.000

[Sam Rueby]

To be clear, it's the same precision as SQL datetime, not a .NET DateTime.

[jKlaus]

That is correct, I assumed everyone would understand the context but its worth specifically stating.

[Pankaj Parkar]

Thanks for showing that statistics +1 for it, datetime2 is awesome(Winner)

[TamusJRoyce]

Another option is to use an indexed view with the column converted as a datetime for compatibility. You would need to be able to point the app to the view, however.

[Suncat2000]

Time zone support with DATETIMEOFFSET is a misnomer. It only stores a UTC offset for a specific instant in time, not a time zone.

[Tom]

@Porad: What exactly is the benefit in practice of being " "more portable" due to being "SQL Standard"? That is besides making you write significantly more code that is significantly less readable / maintainable for a "port" to another RDBMS that is likely never to occur for the life of that code. Other than perhaps Microsoft-provided SQL Server tools and Drivers (if even), are there any apps that actually rely the specific Bit-level representations of the DateTime2 Type (or any other SQL Server Type for that matter)? See the Cons in my 7/10/17 Answer below for why I'm asking.

[Tom]

@Iman Abidi: According to Oskar Berggren's comment dated September 10, 2014 at 3:51 pm on the "SQLHINTS- DateTime Vs DateTime2" article you referenced: "datetime2(3) is NOT the same as datetime. They will have the same number of digits, but the precision of datetime is 3.33ms, while the precision of datetime2(3) is 1ms."

[Tom]

@marc_s: I see plenty of significant "benefits" other than "backward compatibility" to using DateTime vs. DateTime2. See the Cons section of my Answer dated 7/10/17 below.

[Tom]

@PankajParkar: Woah, not so fast. You might want to look at the Cons section of my Answer dated 7/10/17 below.

[Tom]

@EBarr: Re. "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": I strongly disagree. See the Cons section of my Answer dated 7/10/17 below. In short, those benefits are likely unneeded (outside engineering/scientific apps) and therefore not worth the loss of benefits MUCH more likely needed, the much easier (even considering workarounds) ability to implicitly / explicitly convert to a floating-point numeric (# of days incl. if appl., fractions since min date-time) value for +, - and avg.

[Tom]

@Adam Porad: Also, all those benefits are likely unneeded (outside of engineering or scientific apps) and therefore not worth the loss of benefits much, MUCH more likely needed: the much easier (even considering workarounds) ability to implicitly / explicitly convert to a floating-point numeric (# of days incl. if appl., fractional days since min date-time) value for additions, subtractions, minimums, maximums and averages. See the Cons in my 7/10/17 Answer below for details.

[EBarr]

Like the contrarian view - it points out the c# side of the equation. Combined with all the other "pros" it will allow people to make a good choice based on where they want to take their pain.

[Tom]

@EBarr: Only the Cons #1 part of my "'contrarian view'" "points out the c# side of the equation". The rest (Cons #'s 2.2.1 - 2.2.3), which like I said are the far more likely needed benefits (of DateTime), are all related to effects on SQL Server Queries and statements.

[AlwaysLearning]

Just so we're clear here the datetime and datetime2 data types were both introduced in SQL Server 2008. You also get Operand type clash: date is incompatible with int from the date type which has been around since day dot. All three data types work just fine with dateadd(dd, 1, ...) though.

[Paul McCarthy]

This is not clear. I have a SQLServer 2005 database with a datetime field in it.

[HamsterWithPitchfork]

Who comes up with these function names? Any other decent programming language wouldn't accept a newer version where you add "2" to the name. What's next, datetime3 ?

[Dai]

How does datetime2 use less storage space than datetime and yet offer a larger range and higher precision?

[RBerman]

Re 2.2.1 -- it is considered an unsafe practice to do arithmetic on dates, and the preferred way is always to use DateAdd and related functions. This is best practice. There are serious liabilities to doing date arithmetic, not the least of which is it doesn't work for most date types. A few articles: sqlservercentral.com/blogs/… sqlblog.org/2011/09/20/…

[brianary]

@Tom Portability does put you at an advantage for switching vendors rather than being resigned to accepting whatever licensing cost a specific vendor decides to raise you to, it also makes switching to a new model like a cloud-based RDBMS easier. But maybe most importantly, it means you can seek help in the wider SQL community, rather than limiting yourself to the community for a given product (which can often be run by the company that makes the product, and may eventually have an interest in moderation that conflicts with your needs).

[Tom]

@RBerman: Re. "unsafe": It's only unsafe with certain date types (like the DateTime2 I already mentioned (due to high chance of overflow)). Re. "desn't work for most date types": You only need it to work with one, and most dates in most apps will likely never need to be converted to another date type for their entire life times (except maybe, like I also mentioned, DateTime2 to DateTime (e.g. to do "arithmetic on dates" ;P). Given that, it's not worth all the extra coding in not only programmed but also ad-hoc research queries to use a non-arithmetic friendly date type.

[Mark Freeman]

One thing I ran into is that taking the MAX() of a DATETIME2(7) results in an INT. So if you compare the original value with the MAX of that column for a group of rows, you only get precision to the minute in the comparison and don't get the expected results when trying to find the latest entry in a group. That was...unexpected.

[ruffin]

Heavy caveat on that MS recommendation: "for new work". I would take that to mean if you have an existing, legacy app, you'd need a pretty stark use case ("These scientific tests fail when repeated b/c the date precision is too low!") to swap, and I don't think that I'd mix the two -- casts galore for comparisons, I believe. Also, related/interesting: datetime might wrap datetime2 in SQL Server 2017+.

[ruffin]

@Dai pretty sure the answer explains this. If you use datetime2(3) you save space for an analog for datetime. If you declare a higher precision (not an option in datetime), you don't save space, but you do gain precision, natch. TL;DR The space savings is for equal precision.

[Milney]

> "SQL Server can’t use statistics properly for Datetime2 columns, due to a way data is stored that leads to non-optimal query plans, which decrease the performance" Citation needed

[robotik]

@Milney it is quoted from the article mentioned (3rd paragraph from the end) - towardsdatascience.com/…