Jamie Thomson (10/24/2007)Good summary. I still want to know what happens if I request the number of nanoseconds between Year 0001 and 9999 using datediff. Cos as far as I know datediff won't support a number as big as that.

For what it's worth right now - Datetime2 seems to have a precision of 100 nanosecond only (meaning you won't be able to drill down to 1ns increments), so that should still fit into 8 bytes. Not sure how they're going to implement datediff increments to handle that.

----------------------------------------------------------------------------------Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Great article. Thanks for the insight. As a Reporting Services developer, I was hoping to find out that the TIME data type would be able to store values beyond 24 hrs. When developing charts in SSRS 2005, I noticed that it will not allow time as a data point in the y-axis (e.g. retrieval time or duration, that can last more than 24 hrs). I was hoping that would be resolved in 2008.

The most frustrating aspect of this is that Excel can chart aggragated time in Excel with ease so it is difficult to explain why SSRS cannot when working with clients.

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 didprint cast(getDate() as float) -- this returned 39379.5 despite the time currently being 2007-10-26 11:19amprint cast(39379.5 as datetime) --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 print cast(0 as datetime) 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? :D )

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

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!

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

Tibor Karaszi touches on this subject in the book Inside SQL Server 2005 :T-SQL Programming.

In 1752 many countries that were still using the julian calendar switched over to the Gregorian calendar. If you allow dates prior to 1753 then you need to know which country you are talking about.

My opinion is that restrictions are useful in many applications but there needs to be the capacity to remove them. There is nothing worse that having a datatype waving its finger at you saying "mmm..hmmm...I don't think so...".

If I want the date to say 1/1/0000 I should have some option to do so. If I want to store a time as 54:34:24.567 i would like to have the option to do so. I appreciate though that having these options may have some design challenges for the system based date and time functions.

As an example a time datatype that is not limited to 24 hours would be very handy in certain applications. To store a duration currently it is best to store a numeric value representing minutes, seconds hours etc and use the dateadd function to add this duration on top of your start time.

Formatting this numeric into the format 'hh:mm:ss' in result sets is a bit of a mess. A system datatype that could store this duration as 'hh:mm:ss.sss' without the hourly limit could alleviate some of these complications from a development perspective although may complicate things from sql server architecture perspective.