Don't forget this date.

  • SqlOnMyMind

    SSCertifiable

    Points: 5027

    Comments posted to this topic are about the item Don't forget this date.

  • malleswarareddy_m

    SSCertifiable

    Points: 5847

    Hi,

    When I using SQL Server it show default of SQL Server as 1900-01-01.I thought that it was the least date.

    But my answer is wrong.

    Malleswarareddy
    I.T.Analyst
    MCITP(70-451)

  • m--S3qU3L

    Hall of Fame

    Points: 3706

    When I insert the datatime row into a table as below, I get different result. Any idea?

    use tempdb

    go

    create table test1 (dat datetime)

    go

    insert into test1 values('1753-01-01')

    select * from test1

    RESULT:

    1753-01-01 00:00:00.000

    insert into test1 values(1753-01-01)

    select * from test1

    RESULT:

    1904-10-18 00:00:00.000

    :unsure:

    Thanks

    big[M]

    John

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    bigM (4/2/2010)


    When I insert the datatime row into a table as below, I get different result. Any idea?

    use tempdb

    go

    create table test1 (dat datetime)

    go

    insert into test1 values('1753-01-01')

    select * from test1

    RESULT:

    1753-01-01 00:00:00.000

    Here you specify a string constant, using the 'yyyy-mm-ddd' format. SQL Server will convert the string constant to a date. Although the 'yyyy-mm-dd' format is not guaranteed to be interpreted correctly in all cases (only 'yyyymmdd', 'yyyy-mm-ddThh:mm:ss' and 'yyyy-mm-ddThh:mm:ss.mmm' are guaranteed), I have not found cases where it is misinterpreted. (And with month and day both equal to 1, it would in this case not even make a difference). So the conversion results in the datetime value equivalent to January 1st, 1753.

    insert into test1 values(1753-01-01)

    select * from test1

    RESULT:

    1904-10-18 00:00:00.000

    :unsure:

    In this case, there are no quotes. SQL Server will interpret 1753-01-01 as an expression that consists of three integers and two subtraction symbols. It will compute the result, which is the integer value 1751. This then has to be stored in a datetime column, so implicit integer to datetime conversion kicks in. And that conversion rule is that the resulting datetime value is computed by adding the integer number of days to the base date of Jan 1st, 1900. The result, October 18th, 1904, is apparently exactly 1751 days after this base date.


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Paul White

    SSC Guru

    Points: 150442

    Hugo Kornelis (4/2/2010)


    Here you specify a string constant, using the 'yyyy-mm-ddd' format. SQL Server will convert the string constant to a date. Although the 'yyyy-mm-dd' format is not guaranteed to be interpreted correctly in all cases (only 'yyyymmdd', 'yyyy-mm-ddThh:mm:ss' and 'yyyy-mm-ddThh:mm:ss.mmm' are guaranteed), I have not found cases where it is misinterpreted.

    I believe the ODBC formats are guaranteed too, since they have a fixed format definition, do not depend on DATEFORMAT, and are multi-language.

    On the downside, the ODBC formats are not an international standard, only return DATETIME (not any of the new 2008 types), and are non-deterministic. Not ideal, then 🙂

    Examples:

    SELECT { ts '2010-04-02 23:49:30.723' };

    SELECT { d '2010-04-02' };

    SELECT { t '23:49:30.723' };

    Books Online:

    Using Date and Time Data

  • Paul White

    SSC Guru

    Points: 150442

    I wonder how long it will be before someone complains that this QotD is misleading and incorrect since the question specifies "(yyyy/mm/dd)" format and the answers are all in YYYY-MM-DD format?

    :laugh:

  • SanjayAttray

    SSChampion

    Points: 13157

    The ultimate guide to the datetime datatypes

    http://www.karaszi.com/SQLServer/info_datetime.asp#Why1753

    SQL DBA.

  • Voitek

    Ten Centuries

    Points: 1366

    I did a cast(0 as datetime), but then I thought hmm. can negatives be converted to datetime? I picked 64000, but that failed, so then I went incrementally to -53690 beyond which the cast fails.

    I don't think the QOD is misleading. The oldest date you can store is -53690, which is 1-1-1753.

  • Paul White

    SSC Guru

    Points: 150442

    Voitek (4/2/2010)


    I don't think the QOD is misleading.

    Neither does anyone else...so far. Give it time 😉

  • vk-kirov

    SSCertifiable

    Points: 7686

    Why the question specifies the slash datetime format (yyyy/mm/dd) while all the answers are in the dash datetime format (yyyy-mm-dd)? This is completely misleading and incorrect! 😛

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    *groan*


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Paul White

    SSC Guru

    Points: 150442

    vk-kirov (4/2/2010)


    Why the question specifies the slash datetime format (yyyy/mm/dd) while all the answers are in the dash datetime format (yyyy-mm-dd)? This is completely misleading and incorrect! 😛

    Awesome! Very funny :laugh:

  • vk-kirov

    SSCertifiable

    Points: 7686

    By the way, which event took place on 1776-01-01?

  • Daniel Bowlin

    SSC-Dedicated

    Points: 34566

    I knew the answer, but I didn't know the bit about the Gregorian calendar....interesting.

  • sjimmo

    SSChampion

    Points: 11139

    My only complaint is that it is true.

    Had to do a conversion back in 6.5 days, and maintain dates going back to 1600's. Had to be a bit creative on that;-)

    Good question

    Steve Jimmo
    Sr DBA
    “If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan

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

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