DateAdd Function

Question

Post reply

DateAdd Function

Manish Sinha

Ten Centuries

Points: 1023
More actions
March 3, 2009 at 9:43 pm

#201825

Comments posted to this topic are about the item DateAdd Function

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

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

john.arnott SSChampion Points: 11882 More actions · Answer 1

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 More actions · Answer 2

Clicked on the wrong radio button :crying:

"Keep Trying"

Mike John SSCertifiable Points: 7186 More actions · Answer 3

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 More actions · Answer 4

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 More actions · Answer 5

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 SSC-Dedicated Points: 31715 More actions · Answer 6

***********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 asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

David Burrows SSC Guru Points: 65138 More actions · Answer 7

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 More actions · Answer 8

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: 33506 More actions · Answer 9

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: 33506 More actions · Answer 10

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 More actions · Answer 11

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: 2398 More actions · Answer 12

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: 65138 More actions · Answer 13

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 More actions · Answer 14

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: