Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

ISNULL Expand / Collapse
Author
Message
Posted Wednesday, November 23, 2011 9:13 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, October 9, 2014 8:27 AM
Points: 1,639, Visits: 1,984
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.


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

Post #1211136
Posted Wednesday, November 23, 2011 10:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:53 AM
Points: 1,176, Visits: 778
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.


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.
Post #1211218
Posted Wednesday, November 23, 2011 10:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 10:48 AM
Points: 4,126, Visits: 3,427
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.
Post #1211225
Posted Wednesday, November 23, 2011 12:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:07 AM
Points: 6,040, Visits: 8,322
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.



Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Post #1211278
Posted Wednesday, November 23, 2011 1:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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), '')

Post #1211347
Posted Wednesday, November 23, 2011 5:59 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 1:04 PM
Points: 900, Visits: 1,490
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
Post #1211411
Posted Friday, November 25, 2011 1:33 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:42 PM
Points: 13,239, Visits: 11,018
Nice question, thanks.



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1211840
Posted Friday, November 25, 2011 11:54 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 10:28 PM
Points: 568, Visits: 73,766
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.
Post #1212048
Posted Thursday, December 1, 2011 6:06 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, April 27, 2014 7:45 PM
Points: 1,589, Visits: 253
Almost picked none would fail, then did a quick investigation. Learned something new today. Thanks for the question.

http://brittcluff.blogspot.com/
Post #1214562
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse