Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 conversion explanation requested Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, March 26, 2013 6:00 AM
 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. <>
Post #1435414
 Posted Tuesday, March 26, 2013 6:04 AM
 SSChampion Group: General Forum Members Last Login: Today @ 9:41 PM Points: 14,556, Visits: 38,433
 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!
Post #1435415
 Posted Tuesday, March 26, 2013 6:22 AM
 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. <>
Post #1435427
 Posted Tuesday, March 26, 2013 6:32 AM
 SSChampion Group: General Forum Members Last Login: Today @ 9:41 PM Points: 14,556, Visits: 38,433
 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!
Post #1435430
 Posted Tuesday, March 26, 2013 7:51 AM
 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. <>
Post #1435487
 Posted Tuesday, March 26, 2013 8:14 AM
 SSChampion Group: General Forum Members Last Login: Today @ 9:41 PM Points: 14,556, Visits: 38,433
 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 1Arithmetic 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!
Post #1435509
 Posted Tuesday, March 26, 2013 9:59 AM
 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. <>
Post #1435594

 Permissions