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

webrunner (3/4/2009)

---

***********SPOILER ALERT***********



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

HanShi (3/4/2009)

---

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

SELECT DATEADD(MINUTE,30,DATEADD(hour,7,'2009-02-09 10:00:00:000'))

2009-09-02 17:30:00.000

David: we are going off topic.

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

Ninja's_RGR'us (3/4/2009)

---

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.

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