Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DateAdd Function


DateAdd Function

Author
Message
Manish Sinha
Manish Sinha
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 98
Comments posted to this topic are about the item DateAdd Function
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1466 Visits: 3059
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
ChiragNS
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2409 Visits: 1865
Clicked on the wrong radio button Crying

"Keep Trying"
Mike John
Mike John
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2672 Visits: 5958
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
Shaun McGuile
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 1060
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
Shaun McGuile
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 1060
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. Smile

Hiding under a desk from SSIS Implemenation Work Crazy
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3027 Visits: 3747
***********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

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"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
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7961 Visits: 9407
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
Shaun McGuile
Mr or Mrs. 500
Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)Mr or Mrs. 500 (585 reputation)

Group: General Forum Members
Points: 585 Visits: 1060
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
HanShi
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3048 Visits: 3625
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’! **
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search