I agree, datetime2 is very oracle-ish and BigDateTime is disgustingly obvious 🙂

They cannot play with datetime as it (historically) has been stored as a float, much the same way many programming languages deal with datetime values. From what I've seen, they all seem to share a common zero date so that the value 39379.5 represents midday on 26th October 2007 (although I've never quite trusted this - see below).

In QA, I did

[font="Courier New"]print cast(getDate() as float)[/font] -- this returned 39379.5 despite the time currently being 2007-10-26 11:19am

[font="Courier New"]print cast(39379.5 as datetime)[/font] --this returned 2007-10-26 12:00pm

Correct me if I'm wrong, but the article gave some time in the 1700's as the earliest date that could be represented in SQL Server.

If I do [font="Courier New"]print cast(0 as datetime) [/font] I get "Jan 1 1900 12:00AM". However a zero datetime, when displayed in many software packages, comes up as 1899-30-12 - two days different (which is float -2 in SQL using the cast statement)... I've never seen a good explanation for this (anyone care to offer one? 😀 )

Finally, if the zero date is the start of 1900 and the dates can go up to the year 9999, why can they not go back as far using negative floats? Going back <200 years whilst being able to go forward several thousand years is a bit odd... Perhaps my assumption about floats being behind the scenes is wrong? I haven't bothered checking BOL for this lately so if it was obvious in there please be nice 😀

Anyhow, it was a good introductory article. I was curious about the byte sizes of the types but someone's been kind enough to post them too!