DateAdd Function

  • Manish Sinha

    Ten Centuries

    Points: 1023

    Comments posted to this topic are about the item DateAdd Function

  • john.arnott

    SSChampion

    Points: 11882

    For one additional degree of difficulty: What's the result from this query?

    select DATEADD(HOUR,5,DATEADD(MINUTE,-59,'2009-02-09 12:34:56.789'))

    a) 2009-02-09 07:34:57.789

    b) 2009-02-09 16:35:56.789

    c) 2009-02-09 16:33:56.790

    d) 2009-02-09 16:35:56.790

  • ChiragNS

    One Orange Chip

    Points: 26137

    Clicked on the wrong radio button :crying:

    "Keep Trying"

  • Mike John

    SSCertifiable

    Points: 7186

    Alternative answer is :

    2009-09-02 17:30:00.000 -- which is exactly what you will see if you are configured for a UK date format. ie the dateadds behave as described by the input date is in an ambiguous format... was it the 2nd Sept or 9th Feb?

    Mike John

  • Shaun McGuile

    SSCarpal Tunnel

    Points: 4111

    Chirag (3/4/2009)


    Clicked on the wrong radio button :crying:

    damn thats bad luck on so easy a question :w00t:

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • Shaun McGuile

    SSCarpal Tunnel

    Points: 4111

    Mike John (3/4/2009)


    Alternative answer is :

    2009-09-02 17:30:00.000 -- which is exactly what you will see if you are configured for a UK date format. ie the dateadds behave as described by the input date is in an ambiguous format... was it the 2nd Sept or 9th Feb?

    Mike John

    is always YYYY-MM-DD when written this way. 🙂

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • webrunner

    One Orange Chip

    Points: 29863

    ***********SPOILER ALERT***********

    john.arnott (3/3/2009)


    For one additional degree of difficulty: What's the result from this query?

    select DATEADD(HOUR,5,DATEADD(MINUTE,-59,'2009-02-09 12:34:56.789'))

    a) 2009-02-09 07:34:57.789

    b) 2009-02-09 16:35:56.789

    c) 2009-02-09 16:33:56.790

    d) 2009-02-09 16:35:56.790

    I'm going to go with:

    b) 2009-02-09 16:35:56.789

    UPDATE: I ran the result and got c) 2009-02-09 16:33:56.790. (My original second choice.) Why do the milliseconds get rounded up?

    Thanks,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and says Can I join you?
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • David Burrows

    SSC Guru

    Points: 64471

    is always YYYY-MM-DD when written this way

    I'll pretend you really did not write or mean that :crazy:

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Shaun McGuile

    SSCarpal Tunnel

    Points: 4111

    David Burrows (3/4/2009)


    is always YYYY-MM-DD when written this way

    I'll pretend you really did not write or mean that :crazy:

    I wrote it and meant it.

    ISO standard datetime YYYY-MM-DD:HH:MM:SS

    What do you think I meant, it can only ever be this way.

    (SQL Server handles the yyyy-xx-xx dates this way, in functions as far as I have seen both in US and UK settings).

    Hiding under a desk from SSIS Implemenation Work :crazy:

  • HanShi

    SSC-Dedicated

    Points: 33186

    john.arnott (3/3/2009)


    For one additional degree of difficulty: What's the result from this query?

    select DATEADD(HOUR,5,DATEADD(MINUTE,-59,'2009-02-09 12:34:56.789'))

    a) 2009-02-09 07:34:57.789

    b) 2009-02-09 16:35:56.789

    c) 2009-02-09 16:33:56.790

    d) 2009-02-09 16:35:56.790

    I think it's answer B. Microseconds are rounded to increments of .000, .003, or .007 seconds, second 59 minutes are deducted and finally 5 hours are added.

    But at this moment I am not able to check it myself because I don't have a SQL environment available.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • HanShi

    SSC-Dedicated

    Points: 33186

    Mike John (3/4/2009)


    Alternative answer is :

    2009-09-02 17:30:00.000 -- which is exactly what you will see if you are configured for a UK date format. ie the dateadds behave as described by the input date is in an ambiguous format... was it the 2nd Sept or 9th Feb?

    Mike John

    That's only an issue if the year is mentioned at the end (02-09-2009). When the year is mentioned first it's always yyyy-mm-dd, many times also noted without the dashes (20090902).

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    webrunner (3/4/2009)


    ***********SPOILER ALERT***********

    john.arnott (3/3/2009)


    For one additional degree of difficulty: What's the result from this query?

    select DATEADD(HOUR,5,DATEADD(MINUTE,-59,'2009-02-09 12:34:56.789'))

    a) 2009-02-09 07:34:57.789

    b) 2009-02-09 16:35:56.789

    c) 2009-02-09 16:33:56.790

    d) 2009-02-09 16:35:56.790

    I'm going to go with:

    b) 2009-02-09 16:35:56.789

    UPDATE: I ran the result and got c) 2009-02-09 16:33:56.790. (My original second choice.) Why do the milliseconds get rounded up?

    Thanks,

    webrunner

    It's the limit of the precision in ms. If the last digit is not a possible value, it gets rounded up or down depending on the closest number.

  • Dietmar Weickert

    SSCrazy

    Points: 2238

    HanShi (3/4/2009)


    Mike John (3/4/2009)


    Alternative answer is :

    2009-09-02 17:30:00.000 -- which is exactly what you will see if you are configured for a UK date format. ie the dateadds behave as described by the input date is in an ambiguous format... was it the 2nd Sept or 9th Feb?

    Mike John

    That's only an issue if the year is mentioned at the end (02-09-2009). When the year is mentioned first it's always yyyy-mm-dd, many times also noted without the dashes (20090902).

    Well, that's exactly what I would have expected. Nevertheless, when I copy & pasted the given statement

    SELECT DATEADD(MINUTE,30,DATEADD(hour,7,'2009-02-09 10:00:00:000'))

    into SS Studio Express and pressed F5, my result was

    2009-09-02 17:30:00.000

    Rather confuses me... :unsure:

    Best regards,
    Dietmar Weickert.

  • David Burrows

    SSC Guru

    Points: 64471

    YYYY-xx-xx will be interpreted as

    YYYY-MM-DD or YYYY-DD-MM depending on the connection's language setting

    Far away is close at hand in the images of elsewhere.
    Anon.

  • Shaun McGuile

    SSCarpal Tunnel

    Points: 4111

    David: we are going off topic.

    I think we all agree that again we have a QoD which is not locale neutral.

    This can cause issues with selecting a correct answer.

    We need better questions. 🙂

    Hiding under a desk from SSIS Implemenation Work :crazy:

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

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