SQL Server Performance

What is min date of "-53690"?

Discussion in 'General Developer Questions' started by eramgarden, Sep 19, 2006.

Page 1 of 2

  1. eramgarden New Member

    ok, this is my coworker has ..this is his first time writing a sql stored proc...

    declare @mindate as Datetime

    Select @mindate=-53690

    Then in his select/update statements he has:

    select whatever from table1 where datefield > @mindate

    -- That @mindate = -53690 comes to "Jan 1 1753 12:00AM" when I do a print on it in QA.

    I cant find any info in BOL for it. Googled and nothing.

    Is that a good way of comparing date to "1753'?? anyone used this method??
    eramgarden, Sep 19, 2006
    #2

  2. Adriaan New Member

    declare @mindate as Datetime
    Select @mindate=-53690

    SELECT @mindate

    Which dat is returned?

    It is pretty well known that 1-1-1753 is the earliest date you can enter in SQL Server ...

    Adriaan, Sep 19, 2006
    #2

  3. eramgarden New Member

    I know and that's what i said as well..

    but why do it that way..where in BOL is documented to use "-53690"? any disadvantages to using a date like that?
    eramgarden, Sep 19, 2006
    #2

  4. FrankKalis Moderator

    -53690 is the Integer representation of the minimum date value allowed in SQL Server. DATETIME values are internally stored as 2 4-bytes INTEGERs. The first representing the days elapsed since the base date of 01.01.1900, the second representing the number of clock ticks since midnight.

    I think back in those days where there haven't been sophisticated DATETIME data types and date functions, it was quite common to use integers and internally integer arithmetic is still used in SQL Server when calculating dates, so it doesn't hurt anything when your coworker is using INTs. However, now we do have DATETIME data types and very optimised date functions, it isn't necessary at all to use INTs and you can use DATETIMEs for better readability of the code as a whole.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Sep 19, 2006
    #2

  5. eramgarden New Member

    which method is faster:
    Having the variable set to "-53690", then comparing it to datetime fields
    OR
    Having it set to '1753-01-01 00:00:00", then comparing it to datetime fields

    ?
    eramgarden, Sep 21, 2006
    #2

  6. FrankKalis Moderator

    I doubt that you will see a significant difference in performance between both methods anyway. Probably the INT methods will be a tick faster since it can be directly compared to the first 4-bytes of a DATETIME, while the literal representation would first need to be computed to an INT behind the scenes anyway. So, you might save some computational power by using the INT method, but that is just nothing but my educated guess. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Sep 21, 2006
    #2

  7. ramkumar.mu New Member

    Seems Archeologists cannot use SQL Server DateTime for their information storage [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />And also the future predictors cannot (since the max values is 2958463) [<img src='/community/emoticons/emotion-2.gif' alt=':D' />][<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Thanks,<br />Ram<br /><br />"It is easy to write code for a spec and walk in water, provided, both are freezed..."
    ramkumar.mu, Sep 22, 2006
    #2

  8. FrankKalis Moderator

    Well, regarding the future is SQL Server perfectly in compliance with the SQL standard which defined valid year date values as the range of 0 - 9999. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Sep 22, 2006
    #2

  9. Madhivanan Moderator

    In sql server 2005 also the same ranges?

    Madhivanan

    Failing to plan is Planning to fail
    Madhivanan, Sep 23, 2006
    #2

  10. FrankKalis Moderator

    quote:Originally posted by Madhivanan

    In sql server 2005 also the same ranges?

    Madhivanan

    Failing to plan is Planning to fail
    AFAIK, yes.

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Sep 25, 2006
    #2

  11. chiragkhabaria New Member

    just wondering, what is the algorithm for converting the int feild to the datetime<br /><br />for instance in this case how SQL Server converts -53690 to 1753-01-01 ??<br /><br />any Articles will be help ful.. <br /><br />Thanks [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Chirag
    chiragkhabaria, Sep 25, 2006
    #2

  12. FrankKalis Moderator

    I don't know exactly how SQL Server calculates, but when you search the Google Groups for "Julian Day Number" or "Date Arithmetic or similar terms, you'll get plenty of information about it. Happy googling and lots of fun while trying to adapt C++ code to T-SQL. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Sep 25, 2006
    #2

  13. Adriaan New Member

    Chirag,

    From Books Online, "datetime and smalldatetime":
    quote:Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

    Adriaan, Sep 25, 2006
    #2

  14. Roji. P. Thomas New Member

    quote:Originally posted by chiragkhabaria

    just wondering, what is the algorithm for converting the int feild to the datetime

    for instance in this case how SQL Server converts -53690 to 1753-01-01 ??

    any Articles will be help ful..

    From what I can infer

    '01/01/1900' = 0

    So Today = 38984

    And 1753-01-01 = -53690





    Roji. P. Thomas
    Microsoft SQL Server MVP
    http://toponewithties.blogspot.com
    Roji. P. Thomas, Sep 25, 2006
    #2

  15. FrankKalis Moderator

    I guess Chirags' quesion was more about how to derive to date of 17530101 from the INT -53690. So, something like:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @dt INT<br />SET @dt = -53690<br />SELECT 1900 + (@dt / 365) AS [Year]<br /><br />Year <br />----------- <br />1753<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />...and so on...[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Sep 25, 2006
    #2

  16. chiragkhabaria New Member

    Aha.. Got it.. <br /><br />Thanks Frank and Roji for explaination.. learned something new [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Adraian I guess you misunderstood me... Thanks for the Feedback..<br /><br />Chirag
    chiragkhabaria, Sep 25, 2006
    #2

  17. Twan New Member

    Note that the calc works because it is treating @dt/365 as int...

    incidentally 1753 is the first full year after the last date adjustment in Sept 1752 where 11 days were skipped going from 2nd Sept to 14th Sept

    Cheers
    Twan
    Twan, Sep 25, 2006
    #2

  18. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Twan</i><br /><br />Note that the calc works because it is treating @dt/365 as int...<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Spoiler! [<img src='/community/emoticons/emotion-4.gif' alt=':p' />]<br /><blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><br />incidentally 1753 is the first full year after the last date adjustment in Sept 1752 where 11 days were skipped going from 2nd Sept to 14th Sept<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Actually that's also why I believe that 17530101 is the min date in SQL Server. For all other calculations it would require Julian calendar calculations and conversion, AFAIK. <br />And, honestly, most applications and databases can live very well with a date range 1753 - 9999. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Sep 25, 2006
    #2

  19. Roji. P. Thomas New Member

    Roji. P. Thomas, Sep 25, 2006
    #2

  20. FrankKalis Moderator

    The link at the end of Tibors article is (for calendars in general) much more interesting:
    http://www.tondering.dk/claus/cal/

    --
    Frank Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    FrankKalis, Sep 25, 2006
    #2

  21. kpayne New Member

    Ignore this - I didn't read the second page of the thread.
    kpayne, Sep 26, 2006
    #2
Page 1 of 2

Share This Page