March 14, 2013 at 6:56 am
Toreador (3/14/2013)
Most disappointing. I was sure when I got to the thread I'd see several pages of identical replies saying that it gives an error about assigning variables in the declare statement. What is the world coming to?!
I think most of us use SQL 2008 or later versions and are used to it by now. 🙂
That said, this reminds me why I don't use smalldatetime for anything.
Date - yes (just having dates is awesome!)
Time - yes (just having times is also awesome!)
Datetime - yes by default from old stuff (not the coolest, but it generally works)
DateTimeOffset - default for new date/time columns (we have enough localization problems, storing the offset in the datetime helps us avoid new ones).
March 14, 2013 at 7:02 am
Nice question and correct answer (pity about the locale issue, though).
Unfortunately, the explanation is rather wrong - not surprising, as the BOL page on smalldatetime also gets the rounding wrong.
29.999 seconds is not rounded up to 1 minute in conversion to smalldatetime. What is happening in the code here is than 29.999 seconds is rounded up to 30.000 seconds in the conversion to datetime, and 30.000 seconds is rounded up to 1 minute in the conversion to smalldatetime. For conversion to smalldatetime, values less than or equal to 29.9999999 seconds are rounded down unless a prior conversion to datetime causes rounding up (which can only happen if the original value is greater than 29.99849999 seconds) and values 30.0000000 or more seconds are rounded up. The weirdness of the result here (where the smalldatetime appears to have been rounded in teh wrong direction) isn't caused by the difference between the precisions of smalldatetime and datetime but by the lack of precision in datetime.
I imagine the BOL error is because whoever wrote it thought a string like '1999-09-19 10:00:30.999' was a some sort of datetime literal; well, it isn't, it's a string literal and casting string literals as small datetime invokes a two stage process: first convert to datetime, and then round to smalldatetime - - there is no one stage conversion from string literal to smalldatetime in SQL Server. I think that was a bad decision by the seigners, but they probably justified it on the grounds that it was best if a given string literal represented only one time, which with the two-stage process was sort of true until the introduction of datetime2 in SQL 2005.
You can check that it works like this easily by declaring a datetime2 variable instead of a datetime variable, and converting to smalldatetime directly from that and also converting the datetime2 value to datetime and converting that to smalldatetime. For datetime values ending with values between 29.9985000 seconds and 29.9999999 seconds the two routes will deliver different smalldatetime values.
edit: fix typos and omitted words
Tom
March 14, 2013 at 7:41 am
L' Eomot Inversé (3/14/2013)
Nice question and correct answer (pity about the locale issue, though).Unfortunately, the explanation is rather wrong - not surprising, as the BOL page on smalldatetime also gets the rounding wrong.
29.999 seconds is not rounded up to 1 minute in conversion to smalldatetime. What is happening in the code here is than 29.999 seconds is rounded up to 30.000 seconds in the conversion to datetime, and 30.000 seconds is rounded up to 1 minute in the conversion to smalldatetime. For conversion to smalldatetime, values less than or equal to 29.9999999 seconds are rounded down unless a prior conversion to datetime causes rounding up (which can only happen if the original value is greater than 29.99849999 seconds) and values 30.0000000 or more seconds are rounded up. The weirdness of the result here (where the smalldatetime appears to have been rounded in teh wrong direction) isn't caused by the difference between the precisions of smalldatetime and datetime but by the lack of precision in datetime.
I imagine the BOL error is because whoever wrote it thought a string like '1999-09-19 10:00:30.999' was a some sort of datetime literal; well, it isn't, it's a string literal and casting string literals as small datetime invokes a two stage process: first convert to datetime, and then round to smalldatetime - - there is no one stage conversion from string literal to smalldatetime in SQL Server. I think that was a bad decision by the seigners, but they probably justified it on the grounds that it was best if a given string literal represented only one time, which with the two-stage process was sort of true until the introduction of datetime2 in SQL 2005.
You can check that it works like this easily by declaring a datetime2 variable instead of a datetime variable, and converting to smalldatetime directly from that and also converting the datetime2 value to datetime and converting that to smalldatetime. For datetime values ending with values between 29.9985000 seconds and 29.9999999 seconds the two routes will deliver different smalldatetime values.
edit: fix typos and omitted words
+1
Thanks for the question and a great explanation by Tom
___________________________________________________________________
If I can answer a question then anyone can answer it..trying to reverse the logic.. :hehe:
March 14, 2013 at 8:21 am
Plus one,thanx.
“When I hear somebody sigh, ‘Life is hard,’ I am always tempted to ask, ‘Compared to what?’” - Sydney Harris
March 14, 2013 at 8:28 am
Richard Warr (3/14/2013)
Interestingly, if I run the code, I get:
Msg 242, Level 16, State 3, Line 1The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
But if I change the first line to
DECLARE @Datetime datetime = '2013-02-24T23:59:30'then it works as expected.What do I have that is different (I'm in the UK if that helps)?
Well...
SET LANGUAGE British;
-- The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
SELECT CAST('2013-02-24 23:59:30' AS DATETIME);
SET LANGUAGE us_english;
-- Success.
SELECT CAST('2013-02-24 23:59:30' AS DATETIME);
March 14, 2013 at 8:37 am
demonfox (3/13/2013)
an easy one for the day !!!thanks for the question
+1
March 14, 2013 at 8:45 am
kapil_kk (3/14/2013)
Richard Warr (3/14/2013)
Hugo Kornelis (3/14/2013)
Richard Warr (3/14/2013)
What do I have that is different (I'm in the UK if that helps)?Locale settings.
Contrary to popular belief, yyyy-mm-dd hh:mm:ss and yyyy-mm-dd are NOT a guaranteed safe date format. This can, in some locales, still be interpreted as yyyy-dd-mm.
The only guaranteed safe formats for datetime are:
* yyyymmdd for date only; no seperators.
* yyyy-mm-ddThh:mm:ss or yyyy-mm-ddThh:mm:ss.ttt (where ttt is thousands of a second) for date and time; the dashes, uppercase T, colons (and dot in the second version) are all required seperators.
For the date data type, rules have changed slightly. I know both yyyy-mm-dd and yyyymmdd are guaranteed for date. I don't know if there are more changes.
Thank you Hugo. Pleased to say that I always use the "T" format (which I believe to be ISO) and encourage colleagues to do the same!
I have never used this 'T' format :w00t:
+1
I should be careful... :w00t:
March 14, 2013 at 8:47 am
L' Eomot Inversé (3/14/2013)
Nice question and correct answer (pity about the locale issue, though).Unfortunately, the explanation is rather wrong - not surprising, as the BOL page on smalldatetime also gets the rounding wrong.
29.999 seconds is not rounded up to 1 minute in conversion to smalldatetime. What is happening in the code here is than 29.999 seconds is rounded up to 30.000 seconds in the conversion to datetime, and 30.000 seconds is rounded up to 1 minute in the conversion to smalldatetime. For conversion to smalldatetime, values less than or equal to 29.9999999 seconds are rounded down unless a prior conversion to datetime causes rounding up (which can only happen if the original value is greater than 29.99849999 seconds) and values 30.0000000 or more seconds are rounded up. The weirdness of the result here (where the smalldatetime appears to have been rounded in teh wrong direction) isn't caused by the difference between the precisions of smalldatetime and datetime but by the lack of precision in datetime.
I imagine the BOL error is because whoever wrote it thought a string like '1999-09-19 10:00:30.999' was a some sort of datetime literal; well, it isn't, it's a string literal and casting string literals as small datetime invokes a two stage process: first convert to datetime, and then round to smalldatetime - - there is no one stage conversion from string literal to smalldatetime in SQL Server. I think that was a bad decision by the seigners, but they probably justified it on the grounds that it was best if a given string literal represented only one time, which with the two-stage process was sort of true until the introduction of datetime2 in SQL 2005.
You can check that it works like this easily by declaring a datetime2 variable instead of a datetime variable, and converting to smalldatetime directly from that and also converting the datetime2 value to datetime and converting that to smalldatetime. For datetime values ending with values between 29.9985000 seconds and 29.9999999 seconds the two routes will deliver different smalldatetime values.
Thanks for the gr8 explanation... its a new learning for me.
March 14, 2013 at 9:14 am
Hugo Kornelis (3/14/2013)
Richard Warr (3/14/2013)
What do I have that is different (I'm in the UK if that helps)?Locale settings.
Contrary to popular belief, yyyy-mm-dd hh:mm:ss and yyyy-mm-dd are NOT a guaranteed safe date format. This can, in some locales, still be interpreted as yyyy-dd-mm.
The only guaranteed safe formats for datetime are:
* yyyymmdd for date only; no seperators.
* yyyy-mm-ddThh:mm:ss or yyyy-mm-ddThh:mm:ss.ttt (where ttt is thousands of a second) for date and time; the dashes, uppercase T, colons (and dot in the second version) are all required seperators.
For the date data type, rules have changed slightly. I know both yyyy-mm-dd and yyyymmdd are guaranteed for date. I don't know if there are more changes.
Interesting, I would be one of the people who thought yyyy-mm-dd was a safe date format.
Thanks Hugo, I'd better do a bit of reading.
March 14, 2013 at 11:38 am
March 14, 2013 at 1:13 pm
Thanks for an easy one!
March 14, 2013 at 6:09 pm
Wow Tom. Epic yet fantastic explanation of the inner working off this qotd.
Thanks very much for that. I thought it was interesting when I posted it but you really took it to the next level.
Cheers
Dave
David Bridge
David Bridge Technology Limited
www.davidbridgetechnology.com
March 14, 2013 at 8:30 pm
sestell1 (3/14/2013)
Hugo Kornelis (3/14/2013)
Richard Warr (3/14/2013)
What do I have that is different (I'm in the UK if that helps)?Locale settings.
Contrary to popular belief, yyyy-mm-dd hh:mm:ss and yyyy-mm-dd are NOT a guaranteed safe date format. This can, in some locales, still be interpreted as yyyy-dd-mm.
The only guaranteed safe formats for datetime are:
* yyyymmdd for date only; no seperators.
* yyyy-mm-ddThh:mm:ss or yyyy-mm-ddThh:mm:ss.ttt (where ttt is thousands of a second) for date and time; the dashes, uppercase T, colons (and dot in the second version) are all required seperators.
For the date data type, rules have changed slightly. I know both yyyy-mm-dd and yyyymmdd are guaranteed for date. I don't know if there are more changes.
Interesting, I would be one of the people who thought yyyy-mm-dd was a safe date format.
Thanks Hugo, I'd better do a bit of reading.
I learned this a while ago playing with date values.
March 14, 2013 at 8:37 pm
DavidBridgeTechnology.com (3/14/2013)
Wow Tom. Epic yet fantastic explanation of the inner working off this qotd.Thanks very much for that. I thought it was interesting when I posted it but you really took it to the next level.
Cheers
Dave
Thanks Dave. But remember that no-one will comment on questions unless someone poses them. You wrote a question that generated useful comments on the way string literal conversion to datetime depends on locale plus comments on how string conversion to smalldatetime works - that's a wider range of discussion than any of my questions ever provoked, which suggets to me that it was a better question than any of mine. Just remember that much of the credit for useful discussion on your question is yours, not anyone elses, and keep on producing good questions like this one.
Tom
March 15, 2013 at 5:37 am
Interesting question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 15 posts - 16 through 30 (of 35 total)
You must be logged in to reply to this topic. Login to reply