February 21, 2012 at 3:40 pm
I am at a complete loss why this won't convert, BOL says this should be an implicit conversion.
--this is the example...
select isnumeric('123.0') --equals true
select case when isnumeric('123.0') = 1 then CONVERT(int, '123.0') else null end
now the error:
Conversion failed when converting the varchar value '123.0' to data type int.
I know it has to do with the decimal in string but I am not sure why, can some explain this to me?
Please... and Thanks.
February 21, 2012 at 4:04 pm
ColdCoffee (2/21/2012)
ISNUMERIC will accept and approve decimals, dollar symbol and commas as valid characters! So dont belive ISNUMERIC always.This MSDN link has a lot of information on ISNUMERIC.Read them up!
And for your question, 123.0 is not a convertible integer value.
I think the question the OP is getting at is why it won't convert it in text form?
select convert(int, 123.0)
Notice no quotes, works. I did a quick search and this was the closest documentation I could find as to why this is, but it wasn't very satisfying to me. From http://msdn.microsoft.com/en-us/library/ms187928(v=sql.90).aspx
SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.
February 21, 2012 at 4:08 pm
roryp 96873 (2/21/2012)
ColdCoffee (2/21/2012)
ISNUMERIC will accept and approve decimals, dollar symbol and commas as valid characters! So dont belive ISNUMERIC always.This MSDN link has a lot of information on ISNUMERIC.Read them up!
And for your question, 123.0 is not a convertible integer value.
I think the question the OP is getting at is why it won't convert it in text form?
select convert(int, 123.0)
Notice no quotes, works. I did a quick search and this was the closest documentation I could find as to why this is, but it wasn't very satisfying to me. From http://msdn.microsoft.com/en-us/library/ms187928(v=sql.90).aspx
SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.
THe OP's question is why CONVERT(int, '123.0') this wont work and my explanation to it is in my post. if u dont give a "quote" to 123.0 , it easily becomes a decimaL(or numeric/money/float/real) value , which CAN be converted to INTeger.
February 21, 2012 at 4:11 pm
Try these
select convert( int , 123.45 )
,convert( int , 123.55 )
,convert( int , 123.00 )
,convert( int , cast( '123.45' as decimal))
select convert( int , '123.45' )
February 21, 2012 at 4:12 pm
I guess I was wondering if there is some documentation on this behavior. What I found and posted wasn't very satisfying to me. I know it can be converted to int without the quotes. But then why does
select convert(int, '123')
work while
select convert(int, '123.0')
doesn't? Or is this just a product of Microsoft's sometimes confusing documentation?
February 21, 2012 at 4:15 pm
I agree, and it doesn't ring "true" for me for some reason...
this works...
select CONVERT(float, '123.0')
so why doesn't...
select CONVERT(int, '123.0')
especially since MSSQL will convert a float of 123.456 to an int without any problem?
February 21, 2012 at 4:19 pm
I get that it does not work and the work around is simple enough, I was just wondering if someone could explain why? I can't seem to find any clear answer in BOL.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply