Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

Why datetime2?

In SQL Server 2008 a new datatype will be introduced that can support storing of date values from the year 0001 to 9999. This new datatype is called datetime2.

OK, so tell me what's wrong with the following picture:

  • smallint, int, bigint
  • smalldatetime, datetime, datetime2

These are all datatypes in SQL Server 2008. Why on earth have they called this new one datetime2? Surely bigdatetime would be more consistent.

I do worry about the thought processes that go on in building 35 sometimes. Expect a synonymn to appear for datetime2 in SQL Server 201X.



Published Wednesday, October 24, 2007 3:32 PM by jamie.thomson



COMALite J said:

As I understand it, datetime2 was named that for consistency with Oracle DBMS naming conventions.

That said, since Microsoft (and others) have long used the “small-” and “big-” prefixes, it should indeed have been called “bigdatetime” with “datetime2” as a synonym provided solely for Oracle backwards compatibility.

Oh, and the advantage of the new type (whatever you call it) is NOT in increasing the HIGH end of the date range (that remains effectively the same as it was in datetime: the minimum instance of the time precision just prior to midnight 31st December 9999). The LOW end of the range was made considerably EARLIER, from midnight 1st January 1753 in datetime to 1st January 0000 in datetime2 / bigdatetime. This could be of importance to historical databases (but custom data types will still be needed for anyone working with dates B.C.E.). Of more likely use (albeit mainly to scientists and perhaps engineers) is the considerable increase in time precision, from 3.33 milliseconds to 100 nanoseconds.

In other words, the maximum value was in fact increased, albeit only very slightly (human PoV): from 11:59:59.997 PM 31st January 9999 to 11:59:59.9999999 PM 31st January 9999. That’s a considerable difference indeed, if you’re an unstable subatomic particle. :-)

October 24, 2007 4:44 PM

jamie.thomson said:

Ha. Great reply. Thanks COMAlite.

October 24, 2007 4:55 PM

Marcin Kaluza said:

I think the primary reason for introduction of this data type was discrepancy between .NET DateTime with its MinValue of 01/01/01. When using automatic mapping (LINQ) the default value for the DateTime will cause problems as soon as you try saving the objects in the DB.

There is no datetime2 datatype in Oracle as far as I remember, there is however varchar2 (doing pretty much exactly the same job as varchar).

I bet the we'll soon get decimal2 to cover for the lack of precision on the .NET Side.

PS: Ever tried executing "select 1.0/3.0 from dual" on Oracle using any of the available .NET data adapters?? Have fun :)

October 25, 2007 7:53 PM

Matt said:

One difference to int and other big types is that the storage size of a datetime2 is not bigger than a datetime.  In fact, if a lower than max precision is specified, it can be smaller (6 bytes instead of 8 for datetime)


June 3, 2008 8:10 AM

Alex said:

Wow, a great post with great comments, what more could a guy ask for?  (except maybe the people who decided "datetime2" was a good name to be publicly ridiculed)

July 8, 2008 11:42 PM

Mike said:

I would have called it DATETIMEZONE

August 13, 2008 10:50 PM

Mike said:

Strike that - was confused - Jamie agree with you

August 13, 2008 10:53 PM

Kristofer said:

vardatetime would be more in line with existing naming conventions.

You can always create your own derived type with your name-of-choice, e.g.:

create type vardatetime from datetime2

October 22, 2008 4:57 AM
New Comments to this post are disabled

This Blog


Powered by Community Server (Personal Edition), by Telligent Systems