Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Manish Sinha » DateAdd Function
Add to briefcase
26 posts, Page 1 of 3
123»»»

DateAdd Function Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Manish Sinha
Posted Tuesday, March 03, 2009 9:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, May 05, 2010 10:47 PM
Points: 93, Visits: 98
Comments posted to this topic are about the item DateAdd Function
Post #667824
john.arnott
Posted Tuesday, March 03, 2009 11:45 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 7:59 PM
Points: 1,491, Visits: 3,010
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
Post #667864
ChiragNS
Posted Wednesday, March 04, 2009 12:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 5:02 AM
Points: 2,365, Visits: 1,825
Clicked on the wrong radio button

"Keep Trying"
Post #667872
Mike John
Posted Wednesday, March 04, 2009 1:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:17 AM
Points: 2,645, Visits: 4,643
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


Post #667936
Shaun McGuile
Posted Wednesday, March 04, 2009 6:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 04, 2012 9:20 AM
Points: 583, Visits: 1,060
Chirag (3/4/2009)
Clicked on the wrong radio button


damn thats bad luck on so easy a question

Hiding under a desk from SSIS Implemenation Work
Post #668084
Shaun McGuile
Posted Wednesday, March 04, 2009 6:10 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 04, 2012 9:20 AM
Points: 583, Visits: 1,060
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
Post #668086
webrunner
Posted Wednesday, March 04, 2009 6:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 2:08 PM
Points: 2,121, Visits: 2,226
***********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

-------------------
"The chemistry must be respected." - Walter White

"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
Post #668149
David Burrows
Posted Wednesday, March 04, 2009 7:02 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:37 AM
Points: 6,375, Visits: 5,426
is always YYYY-MM-DD when written this way


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

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

Anon.
Post #668165
Shaun McGuile
Posted Wednesday, March 04, 2009 7:08 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, October 04, 2012 9:20 AM
Points: 583, Visits: 1,060
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


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
Post #668183
HanShi
Posted Wednesday, March 04, 2009 7:08 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:33 AM
Points: 1,222, Visits: 1,283
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’! **
Post #668184
« Prev Topic | Next Topic »

Add to briefcase
26 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.