October 15, 2013 at 10:04 am
Hi All,
Why does this conversion fail?
select convert(integer,'15.0271428571429')
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '15.0271428571429' to data type int.
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
October 15, 2013 at 10:09 am
Because an int doesn't have decimal places.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2013 at 10:12 am
If you don't need the .02xxx you can use
select convert(integer,ROUND('15.0271428571429',0))
October 15, 2013 at 10:21 am
GilaMonster (10/15/2013)
Because an int doesn't have decimal places.
I understand that integers don't have decimals but we can convert a decimal to an integer.
Does this mean that SQL can't do an implicit conversion to float/decimal and then to an integer?
I just assumed that since '15.027...' can be converted to a valid number that SQL will be able to convert it to an integer from that implicit conversion.
I'm guessing since it's a 2 step process it doesn't like it.
It's just an internal question I had. I got around it by casting as a float first which is line 2 in the code list.
Is it just me or does it make sense to be able to convert to an integer from a varchar that can be converted into a valid numeric value?
select '15.0271428571429', cast('15.0271428571429' as decimal(15,10)), cast('15.0271428571429' as float)
select convert(integer,cast('15.0271428571429' as float))
select convert(integer,'15.0271428571429')
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
October 15, 2013 at 10:32 am
Want a cool Sig (10/15/2013)
Does this mean that SQL can't do an implicit conversion to float/decimal and then to an integer?
It's perfectly capable of doing an implicit or explicit conversion string to float and float to integer , however your query requested an explicit conversion direct from varchar to int. That is not possible.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2013 at 10:41 am
The reason I brought this up was I have a giant text blob that is parsed and it returns all the values as varchar.
But for some reason it's trying to convert a particular field to an int. The field itself is varchar and it's not used in any other place other then the select clause.
And this only happens when I join another table(TVF). Not used in joins or anywhere else other then the select clause.
I can't post the actual code for the script but this is more of a theoritical question more then anything. Why would sql trying to convert this field when nothing in the script is asking it to convert it?
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
October 15, 2013 at 10:43 am
GilaMonster (10/15/2013)
Want a cool Sig (10/15/2013)
Does this mean that SQL can't do an implicit conversion to float/decimal and then to an integer?It's perfectly capable of doing an implicit or explicit conversion string to float and float to integer , however your query requested an explicit conversion direct from varchar to int. That is not possible.
Thanks Gail, that was my assumption but didn't have any technical reason or didn't remember any technical reason on why it can't.
---------------------------------------------------------------
Mike Hahn - MCSomething someday:-)
Right way to ask for help!!
http://www.sqlservercentral.com/articles/Best+Practices/61537/
I post so I can see my avatar :hehe:
I want a personal webpage 😎
I want to win the lotto 😀
I want a gf like Tiffa :w00t: Oh wait I'm married!:-D
October 15, 2013 at 10:59 am
The following does work:
select convert(integer,'15')
Your example didn't work because data would be truncated. If you convert 2.9999 to 2 in an engineering for finance app the consequences can be serious. That's why SQL errors when conversion would lose data, unless you've already told it what to do by using a ROUND() or other function.
October 15, 2013 at 11:17 am
dan-572483 (10/15/2013)
That's why SQL errors when conversion would lose data, unless you've already told it what to do by using a ROUND() or other function.
There's a huge amount of places where SQL will do implicit conversions that result in losing precision without any errors.
DECLARE @i NUMERIC(3,0)
DECLARE @j-2 INT
SET @i = '15.0271428571429'
SELECT @j-2 = 15.0271428571429
SELECT @i, @j-2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply