

SSC Veteran
Group: General Forum Members
Last Login: Friday, March 20, 2015 8:32 AM
Points: 253,
Visits: 758


Can someone point me to the documentation and or explain why I can not convert a varchar that contains values like .7, 2, 1.3 to decimal(3,1) but I can convert it to float and then to decimal(3,1)?
Thanks.
<>< Livin' down on the cube farm. Left, left, then a right.




SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 14,437,
Visits: 37,834


there must be more to the story; this works without an issue at all:
With MyCTE (val) AS ( SELECT '.7' UNION ALL SELECT '2' UNION ALL SELECT '1.3' ) SELECT *,convert(decimal(3,1),val) FROM MyCTE
could it be that you have data that is larger than 999.9 (so it won't fit in a decimal(3,1), or something that has more precision than 1 decimal place?( ie 333.14?)
Lowell
 help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!




SSC Veteran
Group: General Forum Members
Last Login: Friday, March 20, 2015 8:32 AM
Points: 253,
Visits: 758


Well Lowell, there must be, but I am more confused now than I was yesterday when I hit this issue. I am importing an excel file into table that is created during the import. I then try to load the data, which is nvarchar but I also tried as varchar, into another table where the column is decimal(3,1). Just to be certain I also tried "select convert (decimal(3,1), '1.2')" as a test. Allow I swear it failed yesterday, it is working fine now. As I said I am more confused now than before.
<>< Livin' down on the cube farm. Left, left, then a right.




SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 14,437,
Visits: 37,834


right, but if you try SELECT convert(decimal(3,1),'333.14')
you get a conversion error; that's what i'm thinking is the issue; other daata than the examples you posted are outside of the data boundary; 333.14 will not fit in the decimal 3,1; it would fit in a decimal 5,2 for example.,
Lowell
 help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!




SSC Veteran
Group: General Forum Members
Last Login: Friday, March 20, 2015 8:32 AM
Points: 253,
Visits: 758


I see that part, but the error I was getting, which I should have mentioned in the beginning, was "Error converting data type nvarchar to numeric". And I don't think your example would explain how an intermediate conversion to float would make it all work.
SELECT convert(decimal(3,1),convert(float, '333.14')) I appreciate your interest in helping me understand this.
<>< Livin' down on the cube farm. Left, left, then a right.




SSChampion
Group: General Forum Members
Last Login: Yesterday @ 9:47 PM
Points: 14,437,
Visits: 37,834


Tobar (3/26/2013)
I see that part, but the error I was getting, which I should have mentioned in the beginning, was "Error converting data type nvarchar to numeric". And I don't think your example would explain how an intermediate conversion to float would make it all work. SELECT convert(decimal(3,1),convert(float, '333.14')) I appreciate your interest in helping me understand this. OK sorry: you really get this error in SSMS:
SELECT convert(decimal(3,1),convert(float, '333.14'))
Msg 8115, Level 16, State 6, Line 1 Arithmetic overflow error converting float to data type numeric.
the specific reason is the definition of Decimal(3,1); that says 3 significant digits TOTAL, and one of those digits is to the right of the decimal point.
333.14 is five significant digits, and two to teh right of the decimal point; so it will not fit in the defined size.
Lowell
 help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!




SSC Veteran
Group: General Forum Members
Last Login: Friday, March 20, 2015 8:32 AM
Points: 253,
Visits: 758


Thanks for your help on this.
<>< Livin' down on the cube farm. Left, left, then a right.



