Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

DateAdd Function Expand / Collapse
Author
Message
Posted Tuesday, March 3, 2009 9:43 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

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


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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
Posted Wednesday, March 4, 2009 12:18 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:08 AM
Points: 2,365, Visits: 1,846
Clicked on the wrong radio button

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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 7:23 AM
Points: 2,668, Visits: 5,924
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
Posted Wednesday, March 4, 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 4, 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
Posted Wednesday, March 4, 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 4, 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
Posted Wednesday, March 4, 2009 6:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:33 PM
Points: 2,432, Visits: 2,868
***********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


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


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:30 AM
Points: 7,057, Visits: 7,287
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
Posted Wednesday, March 4, 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 4, 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
Posted Wednesday, March 4, 2009 7:08 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 10:40 AM
Points: 2,480, Visits: 3,023
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 123»»»

Permissions Expand / Collapse