Welcome to EMC Consulting Blogs

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

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

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)

http://msdn.microsoft.com/en-us/library/ms186724(SQL.100).aspx#DateandTimeDataTypes

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)

I would have called it DATETIMEZONE

Strike that - was confused - Jamie agree with you

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