Don't forget this date.

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

  • 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)

  • 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

  • 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/

  • 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

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

  • The ultimate guide to the datetime datatypes

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

    SQL DBA.

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

  • Voitek (4/2/2010)


    I don't think the QOD is misleading.

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

  • 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! 😛

  • *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/

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

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

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

  • 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 33 total)

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