November 23, 2011 at 8:31 am
BOL states that this happens for numeric and decimal so I'm betting it has something to do with the fact that those are the two data types that can take a precision and scale when declared. I tried playing around with making the scale 0 and that didn't work but the check may be such that it doesn't even check the scale.
November 23, 2011 at 8:33 am
JestersGrind (11/23/2011)
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
Though it was not stated, we should consider the question to be on current database engine unless otherwise specified. 2000 is quite old and if we are posting a question relative to 2000 then we should explicitly state that it is for 2000. In other words, I don't think it was an oversight.:-D
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 23, 2011 at 8:37 am
I thought it was interesting that the money works and decimal does not. I did a little more testing. Based on that testing, I would almost say that the ISNULL check implicitly converts the string to a float(or money) for the money data type for the comparison but does not do that for the decimal.
DECLARE @integer INT
,@Float FLOAT
,@Decimal DECIMAL(18,6)
,@Bit BIT
,@Money MONEY
,@String VARCHAR(20) = CAST('' AS FLOAT)
SELECT ISNULL(@Integer,@String),'Int'
SELECT ISNULL(@Float,@String),'Float'
SELECT ISNULL(@Decimal,@String),'Decimal'
SELECT ISNULL(@Bit,@String),'Bit'
SELECT ISNULL(@Money,@String),'Money'
It makes no sense that the decimal behaves this way when the money doesn't.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 23, 2011 at 8:52 am
gary.mazzone (11/23/2011)
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?
Absolutely agree !!
The impossible can be done at once, miracles may take a little longer 🙂
November 23, 2011 at 8:55 am
Tks for the question.
Certainly a little curious as to why only the Decimal behaves in this manor... But this is something that I have run across before in a shop where implicit conversions of data was the normal practice. :crazy:
Happy Thanksgiving to all the (US of) Americans! 😀
November 23, 2011 at 9:13 am
OzYbOi d(-_-)b (11/23/2011)
Certainly a little curious as to why only the Decimal behaves in this manor... But this is something that I have run across before in a shop where implicit conversions of data was the normal practice. :crazy:
It's not just decimal. Numeric will fail as well. And it's also not just implicit conversions. Explicit will fail as well.
select CONVERT(decimal(18,6),'')
go
select CONVERT(numeric(18,6),'')
go
November 23, 2011 at 10:48 am
cfradenburg (11/23/2011)
OzYbOi d(-_-)b (11/23/2011)
Certainly a little curious as to why only the Decimal behaves in this manor... But this is something that I have run across before in a shop where implicit conversions of data was the normal practice. :crazy:It's not just decimal. Numeric will fail as well. And it's also not just implicit conversions. Explicit will fail as well.
select CONVERT(decimal(18,6),'')go
select CONVERT(numeric(18,6),'')
go
Sorry for the abiguity of my earlier statement and although the response was in the narrower context of the question, yes, I could have noted NUMERIC also. 😉
my curiosity stems from the apparent inconsistency where SQL Server allows for other datatypes such as INTEGER, FLOAT & MONEY to implicitly convert empty string to their equivalent zero value, but not for DECIMAL and NUMERIC. 😎
November 23, 2011 at 10:51 am
JestersGrind (11/23/2011)
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
I think that with SQLS2012 RTM and available for download, we should reverse the logic and note specifically when the question also applies to 2000.
November 23, 2011 at 12:10 pm
archie flockhart (11/23/2011)
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.
L' Eomot Inversé (11/23/2011)
That explicit documentation doesn't give any explanation either.
Apparently, I didn't make myself sufficiently clear.
It's true that Books Online doesn't explain WHY SQL Server behaves this way. But that is true of a lot, probably almost all, articles in Books Online. Why is an integer stored in 4 bytes? Why is INTO optional in the INSERT statement? Why is EXEC[UTE] optional at the start of a batch but mandatory at other places? Some of these will have very good reasons, others maybe not. But the point is that they are all documented.
In a question of the day, I think the explanation should make clear why the given answer is correct. Not "why" as in "why did the SQL Server team make it this way", but "why" as in "what documented feature (*) causes this effect"? That's what I was missing in the explanation. The link to the CAST and CONVERT article would have provided that explanation, as this article clearly documents that this is intended behaviour.
November 23, 2011 at 1:59 pm
A good QOD today for a couple reasons. The first, of course, is that it brings to our attention the rather cryptic behavior of SQL not being able to convert an empty string to DECIMAL even though it will happily do so to other numeric types (yeah, "numeric types", but not "type numeric". Golly, I love this sort of crazy terminology....).
The second reason hasn't been mentioned (that I see). I was looking for an option of "All the selects will fail". Why? The code looks as though the author wants to return either an non-null numeric or an empty string, but instead gets a zero. I'm not arguing against SQL's data-typing enforcement as demonstrated here. Rather, I'd emphasize that "proper" coding would obviate the implicit conversion altogether by using a zero as the second operand.
declare @money money
,@bit bit
,@decimal decimal
select isnull(@decimal, 0.0)
select isnull(@money, 0.0)
select isnull(@bit, 0)
And if the empty string were the real objective, an explicit conversion would be needed.
declare @money money
,@bit bit
,@decimal decimal
select isnull(convert(char(20),@decimal), '')
select isnull(convert(char(20),@money), '')
select isnull(convert(char(20),@bit), '')
November 23, 2011 at 5:59 pm
michael.kaufmann (11/23/2011)
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..
Thank you. That's exactly what I was looking for.
Maybe the BOL team should add that kind of comments near the Implicit Conversion chart to minimize confusion?
Anyway a great and intriguing QOTD. Thanks.
Best regards,
Best regards,
Andre Guerreiro Neto
Database Analyst
http://www.softplan.com.br
MCITPx1/MCTSx2/MCSE/MCSA
November 25, 2011 at 1:33 am
Nice question, thanks.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
November 25, 2011 at 11:54 am
I liked this question. It helps add a little perspective when deciding what data type should be used if there is a toss up between float , money or decimal. And if decimal is chosen as the data type then what restrictions there are in handling. Learned a little something new today.
December 1, 2011 at 6:06 am
Almost picked none would fail, then did a quick investigation. Learned something new today. Thanks for the question.
http://brittcluff.blogspot.com/
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply