November 22, 2011 at 8:54 pm
November 23, 2011 at 12:58 am
Interesting question. My gut feeling was that either the conversion to money or none of the conversions would be problematic for the empty string; I am glad I took the time to investigate instead of taking a 50% chance between those two options.
Too bad the explanation is incomplete. Everything in the explanation was already known (to me, that is). The only thing that was new for me was also the only thing that the question's author did not explain - "for some reason" is not a real explanation in my book.
Anyway, since I investigated this before answering I can now affirm that this behaviour is explicitly documented in the CAST and CONVERT topic in Books Online.
November 23, 2011 at 2:23 am
Interesting question, but when i execute bit modified this script, i receive other error:
declare
@decimal decimal(18,6),
@bit bit,
@float float,
@integer int,
@money money
select IsNull(@decimal, '')
select isnull(@bit, '')
select isnull(@integer, '')
select isnull(@money, '')
select isnull(@float, '')
So error was next:
Msg 257, Level 16, State 3, Line 11
Implicit conversion from data type varchar to money is not allowed. Use the CONVERT function to run this query.
I responded to this question as script will fail for SELECT IsNULL(@money, ''), but my answer is wrong 🙁
November 23, 2011 at 3:25 am
Fails on MONEY type as well (using SQL2000)
November 23, 2011 at 4:06 am
Hugo Kornelis (11/23/2011)
[...] Anyway, since I investigated this before answering I can now affirm that this behaviour is explicitly documented in the CAST and CONVERT topic in Books Online.
Thanks to JestersGrind for an interesting question (got it right by researching before answering as by gutt feeling I'd have also said that none would fail :cool:).
And a great big thank you to Hugo for the link to BOL.
In case anyone gets stuck at the conversion table (which indicates implicit conversion between character and numeric data types), keep on reading to find this little comment towards the end:
SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal..
PS: Found this blog by Craig Freedman quite interesting.
-Michael
November 23, 2011 at 4:22 am
In the BOL, I still don't see anything that goes beyond "for some reason" as an explanation of why you can't convert an empty string to decimal, while you can convert it to integer or float or bit. Anyone have any ideas ?
Also, maybe this is maybe not the place to debate this, but is "researching" the question before answering really in the spirit of the QOTD ? Is it any different to just copying the code and running it ?
When I get one of the (many) questions where I don't know the answer, I prefer to try to work out what I think a likely answer would be. I can look at the explanations later, but often learn more by getting it wrong and then finding out why.
November 23, 2011 at 5:08 am
archie flockhart (11/23/2011)
Also, maybe this is maybe not the place to debate this, but is "researching" the question before answering really in the spirit of the QOTD ? Is it any different to just copying the code and running it ?
Yes, it is. In order to tick the right answer(s) after running the code you don't even have to understand why something happened the way it did.
When I research a question, I 1) have to make sure I understand the question (not having English as my mother tongue adds some difficulties sometimes :-)) and 2) by then answering the question I can verify whether the research results I came up with are correct, or whether I've picked the wrong path.
Personally I think that the spirit of the QotD is to learn and whether the learning occurs before or after answering the question is not so important for me.
When I get one of the (many) questions where I don't know the answer, I prefer to try to work out what I think a likely answer would be. I can look at the explanations later, but often learn more by getting it wrong and then finding out why.
I guess that depends on what kind of learning type you are; I prefer to investigate for myself--and get some extra approval by finding out that the author used the same source as I found the most relevant.
Often enough I'd still get it wrong 😛 and continue the learning process by following the explanations given.
Just my 2 cents,
Michael
November 23, 2011 at 5:17 am
It's a good question. And the explanation, despite Hugo's comment, is just fine (except that the reference that Hugo gives would have been useful).
Hugo Kornelis (11/23/2011)
Interesting question. My gut feeling was that either the conversion to money or none of the conversions would be problematic for the empty string; I am glad I took the time to investigate instead of taking a 50% chance between those two options.Too bad the explanation is incomplete. Everything in the explanation was already known (to me, that is). The only thing that was new for me was also the only thing that the question's author did not explain - "for some reason" is not a real explanation in my book.
Anyway, since I investigated this before answering I can now affirm that this behaviour is explicitly documented in the CAST and CONVERT topic in Books Online.
That explicit documentation doesn't give any explanation either. Since MONEY and DECIMAL and NUMERIC are all exact numeric types (see the Data Types topic in BoL) with decimal fractions, there should be some justification for the different behaviours but no reason is given anywhere for this difference in behaviour; why does MONEY, and exact numeric with decimal fraction, behave in this respect like the exact numerics that don't have decimal fractions instead of like the other exact numeric types with decimal fractions? I think that "for some reason" is the only reason that can be offered on the basis of BoL (although of course "because the people who invented this MONEY kludge hadn't a clue" might be some people's guess as to the reason); of course it is not necessary to offer a reason in explanations for QoTD - and in some cases (as illustrated by the potential guessed explanation suggested above) it may better not to. Why don't we have a reason? Not because the question author failed in his explanation - indeed he very accurately (albeit somewhat obliquely) pointed out that there is no documented reason - but because MS has not chosen to tell us the reason (of course there's no reason why it should, although for technical decisions which look this bizarre most technology companies do give reasons).
Tom
November 23, 2011 at 6:03 am
If you comment out Select ISNULL(@Money,'') you get the error on Select ISNULL(@Decimal,'')
Error converting data type varchar to numeric.
So shouldn't the answer be more then 1?
November 23, 2011 at 6:38 am
Thanks for question, JestersGrind !!!
Thanks for your comments Hugo and Tom!!!
November 23, 2011 at 6:41 am
Hugo,
Thanks for the feedback. I agree that the explanation could be better, but through my research on the topic, I couldn't find an adequate explanation for this behavior. Still, I thought this nuance to SQL was interesting enough to form a QotD.
Thanks,
Greg
November 23, 2011 at 6:43 am
Archie,
You are correct. That was an over sight on my part. I didn't test it with SQL 2000. The question should have stated that this was for SQL 2005 and higher. My apologies.
Thanks,
Greg
November 23, 2011 at 7:21 am
L' Eomot Inversé (11/23/2011)
That explicit documentation doesn't give any explanation either. Since MONEY and DECIMAL and NUMERIC are all exact numeric types (see the Data Types topic in BoL) with decimal fractions, there should be some justification for the different behaviours but no reason is given anywhere for this difference in behaviour; why does MONEY, and exact numeric with decimal fraction, behave in this respect like the exact numerics that don't have decimal fractions instead of like the other exact numeric types with decimal fractions? I think that "for some reason" is the only reason that can be offered on the basis of BoL (although of course "because the people who invented this MONEY kludge hadn't a clue" might be some people's guess as to the reason); of course it is not necessary to offer a reason in explanations for QoTD - and in some cases (as illustrated by the potential guessed explanation suggested above) it may better not to. Why don't we have a reason? Not because the question author failed in his explanation - indeed he very accurately (albeit somewhat obliquely) pointed out that there is no documented reason - but because MS has not chosen to tell us the reason (of course there's no reason why it should, although for technical decisions which look this bizarre most technology companies do give reasons).
I believe that MONEY is stored internally as BIGINT, thus the difference in behavior.
November 23, 2011 at 7:45 am
.
L' Eomot Inversé (11/23/2011)
...of course it is not necessary to offer a reason in explanations for QoTD...Why don't we have a reason? Not because the question author failed in his explanation - indeed he very accurately (albeit somewhat obliquely) pointed out that there is no documented reason...
Well said. Great question!
.
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply