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 Wednesday, March 4, 2009 7:11 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:50 AM
Points: 2,262, Visits: 2,725
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’! **
Post #668190
Posted Wednesday, March 4, 2009 7:19 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Thursday, August 28, 2014 4:00 AM
Points: 21,397, Visits: 9,612
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.
Post #668200
Posted Wednesday, March 4, 2009 7:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, August 28, 2014 7:17 AM
Points: 1,608, Visits: 374
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...



Best regards,
Dietmar Weickert.
Post #668214
Posted Wednesday, March 4, 2009 8:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 7,100, Visits: 6,930
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.

Post #668284
Posted Wednesday, March 4, 2009 8:25 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: 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
Post #668297
Posted Wednesday, March 4, 2009 8:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 5:22 AM
Points: 7,100, Visits: 6,930
David: we are going off topic.


Quite

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


Yep ;)

My post was not meant to cause offence or consternation in any way



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

Anon.

Post #668306
Posted Wednesday, March 4, 2009 8:35 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:24 AM
Points: 2,351, Visits: 2,700
Ninja's_RGR'us (3/4/2009)


...
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.


Thanks, but could you explain in more detail what happened? I still don't quite understand how the rounding works in this case.

For example, I ran this:
select DATEADD(MINUTE,-59,'2009-02-09 12:34:56.785')

and got this:
2009-02-09 11:35:56.787

I know SQL is doing something to get that result, but if anything I thought it would end in .786 in this case, yet it ends in .787.

Thanks again!
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 #668316
Posted Wednesday, March 4, 2009 9:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:19 AM
Points: 2,607, Visits: 17,927
webrunner,
.786 isn't a valid millisecond for the datetime datatype...

All datetimes end in either .xx0, .xx3 or .xx7
.xx9, .xx0 and .xx1 are rounded up/down to .xx0
.xx2, .xx3, .xx4 are rounded up/down to .xx3
.xx5, .xx6, .xx8 are rounded up/down to .xx7

Check out the BOL "Date and Time" for an example table.

Thanks,
Chad
Post #668436
Posted Wednesday, March 4, 2009 10:13 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 8:24 AM
Points: 2,351, Visits: 2,700
Chad Crawford (3/4/2009)
webrunner,
.786 isn't a valid millisecond for the datetime datatype...

All datetimes end in either .xx0, .xx3 or .xx7
.xx9, .xx0 and .xx1 are rounded up/down to .xx0
.xx2, .xx3, .xx4 are rounded up/down to .xx3
.xx5, .xx6, .xx8 are rounded up/down to .xx7

Check out the BOL "Date and Time" for an example table.

Thanks,
Chad


Wow, that is odd (well, except for .xx0 :)), but thank you so much for telling me. I never would have figured it out, and there it is spelled out in BOL.

Thanks again,
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 #668458
Posted Wednesday, March 4, 2009 11:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, November 2, 2009 8:46 AM
Points: 875, Visits: 313
I thought it was a dead giveaway that only one answer had the correct date prefix (2009-02-09). The wrong answers had 2009-02-10 and 2009-02-08. Except for clicking on the wrong button on mistake, I can't understand why anyone would miss this question.
Post #668509
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse