DateTime

  • Comments posted to this topic are about the item DateTime

    MH-09-AM-8694

  • Good question. But i thought number of options provided are very less. This makes it easy for the people to guess the answer.

    Kiran Gajendragadkar

    Tech Mahindra bangalore

  • [font="Verdana"]Thanks Kiran, for your valuable comments.

    --Mahesh[/font]

    MH-09-AM-8694

  • I was a little bit disappointed that the answer went into how a datatime data type is stored in two ints, and did not discuss why "1" = "1900-01-02".

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • WayneS (4/27/2010)


    I was a little bit disappointed that the answer went into how a datatime data type is stored in two ints, and did not discuss why "1" = "1900-01-02".

    Same here. So I tried: Select Cast(2 As DateTime)

    Select Cast(0 As DateTime)

    Select Cast(365 As DateTime)

    Select Cast(364 As DateTime) and a few other numbers. It looks like whatever number I put in there is how many days past 1900-01-01 00:00:00.000.

  • SQLServer stores the DateTime in an 8-byte field. The first four bytes are the number of days since 1/1/1900. The other 4 bytes represent some fraction of a second past midnight. This is an implementation choice, it is not a defined interface.

    Try this:

    Select Cast(getdate() As Binary(8))

    You'll get something like:

    0x00009D6500A63B12

    The "date" part here is x0009D65, or 40,293. When you add '1' to a date, you are adding one to the date part of the 8-byte value:

    Select Cast(getdate() + 1 As Binary(8))

    0x00009D6600A6C675

    I think the reason behind the answer is that SQLServer tries its best to guess at what you mean.

    If you have an integer, it treats it as the number of days past 1/1/1900. This is also consitent with something like:

    SELECT Getdate() + 1

    If negative, it's the number of days before 1/1/1900:

    Select Cast(-10 As DateTime) - Dec 22, 1899.

    If you pass in a string to CAST, it tries to make sense of the string:

    Select Cast('1/23/1945' As DateTime) - what you would expect (at least in the US).

    Select Cast('10:32' As DateTime) - 10:32 am on 1/1/1900

    Select Cast('4/27/10' As DateTime) - 4/27/2010!

    Select Cast('4 april 2010' As DateTime) - perfectly valid.

    try these:

    Select Cast(1-1-1900 As DateTime)

    Select Cast(1/1/1900 As DateTime)

    Select Cast(01/01/01 As DateTime)

    Select Cast(01/01/00 As DateTime)

    Hint: They are not date strings. They are arithmetic operations!

    http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

  • Good question. Thanks. 😀

  • Straightforward question. Thanks.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Thanks for the question. One thing that confuses me is why

    Select Cast(0 As DateTime) returns 1900-01-01 00:00:00.000

    and

    select datediff(dd, 0, getdate()) returns 40295 and is calculating the difference from today back to some date in 1753.

    Seems slighly inconsistent, but probably is just my lack of understanding.

    Can anyone clarify?

    Thanks!

  • select datediff(dd, 0, getdate()) returns 40295 and is calculating the difference from today back to some date in 1753.

    This returns 40348 today (2010-06-21). 40348 / 365 = 110.5....

    2010 - 110 = 1900

    Not sure why/how you say it calculates to some date in 1753.

    I'm using SQL 2005.

    Tom

  • Yeah, you are certainly correct. Not sure what I was thinking when I posted this. Maybe got confused with min date in sql server 2005 which I believe is Jan. 1st 1753 and some bad math in my head. No inconsistency in the functions, only in my head....

Viewing 11 posts - 1 through 10 (of 10 total)

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