Date and Time in SQL Server 2008

  • VincentRainardi


    Points: 2905

    Comments posted here are about the content posted at

  • JP-317675

    SSC Eights!

    Points: 955

    "datimeoffset" stores both date and time components and the time zone offset, ranging from 1st January 0001 to 31st December 9999, with accuracy of 100 ns, for example: "2007-10-14 19:35:09.3579284 +02:15. The time zone offset ranges from -14:00 to +14:00.

    How does sql server store this datetimeoffset datatype? Does the offset time indicate how the displayed time should be modified to show the universal time?

    Do you know what implicit conversion ss2k8 performs when you attempt to convert a DatetimeOffset to a normal datetime? Does it add/minus the offset time or does it only return the datetime component?

    I'd check this myself but I dont have the CTP.

    I am glad that ss2k8 is providing these extra date fields. That datetimeoffset is of particular interest as it will be great to display and store the local time while also being aware of what time offset was used at the time. Very handy when daylight savings occur and you want to know the difference between 2:30am before daylight savings stopped and 2:30am after daylight savings stopped.

    -- JP

  • Michel Steiner

    SSC Enthusiast

    Points: 148

    The article says: "The first two are the same as in SQL Server 2000 and 2005. "datetime" stores both date and time components, ranging from 1st January 1753 to 31st December 1999". There is a typo here I guess? isn't it 31st December 9999?

  • BanzaiSi

    SSChasing Mays

    Points: 601


    Fantastic article and a good detailed look at an important area of functionality. Thanks for your time!

    A couple of comments:

    - Third paragraph from the end, it says "CURRENT_TIMESTAMP function is equivalent to GETDATE function" - should this not read CURRENT_DATEIMTE function...?

    - Can the storage sizes of these datatypes be included in the article? I didn't manage to download the Books Online yet for SQL 2008 otherwise I'd include them here 🙁

  • John Nolan

    Mr or Mrs. 500

    Points: 581

    Yuk. Hope they change the name of data type DATETIME2. I just hate that.

  • Nebojsa Ilic


    Points: 1733

    Storage size for new date and time data types are (in bytes):

    date 3

    time 3-5

    datetime2 6-8

    datetimeoffset 8-10

  • Nebojsa Ilic


    Points: 1733

    Talking about date and time data types it's also interesting to mention new function SWITCHOFFSET which returns a datetimeoffset that is changed from the stored offset to a new time zone offset (see BOL).

    Datitime2 is typical Oracle naming convention for new added types and I don't like it too 🙂

  • Daniel Wolford


    Points: 451

    Nice article.

    Are there any new format options that go along with the new data types?

    So when we currently say Convert(varchar,DATETIME,112) are there new options for

    Convert(varchar,TIME,xxx)? I'm sure there must be a nice way of trimming the nonoseconds if you don't want them on the output.



  • rswinehart

    SSC Journeyman

    Points: 93

    Great article. Glad to see there are separate date and time types.

    I hope there will be more Is% functions. I hate to test a value with IsDate then get an error when trying to convert it to smalldatetime. There should be an IsDateTime2, IsDateTime, IsSmallDateTime, IsDate, IsTime, etc.

  • Jamie Thomson


    Points: 11805

    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.

    I've probably got a few more questions that will become evident when I finally get round to digging into this.


  • JJ B

    SSCarpal Tunnel

    Points: 4905

    Datetime2? I agree. Yuck. Why not BigDatetime? We have SmallDatetime...

  • Jamie Thomson


    Points: 11805

    JJ B (10/24/2007)

    Datetime2? I agree. Yuck. Why not BigDatetime? We have SmallDatetime...

    tinyint, smallint, int, bigint

    smalldatetime, datetime, bigdatetime

    The simplicity of your suggestion is so elegant it hurts.

    Silly Microsoft!!!

    Expect a synonymn in SQL Server 201X for datetime2!


  • Matt Miller (4)

    SSC Guru

    Points: 124208

    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?

  • Mark D Powell

    SSCarpal Tunnel

    Points: 4386

    I think the only thing wrong with this is that the article could have used a few examples of the functions so you could see the result format.

  • tthompson

    SSC Journeyman

    Points: 84

    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.



Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply