conversion explanation requested

Question

Post reply

conversion explanation requested

Tobar

SSCarpal Tunnel

Points: 4876
More actions
March 26, 2013 at 6:00 am

#275671

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.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 2

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.

Lowell SSC Guru Points: 323518 More actions · Answer 3

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!

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 4

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.

Lowell SSC Guru Points: 323518 More actions · Answer 5

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!

Tobar SSCarpal Tunnel Points: 4876 More actions · Answer 6

Thanks for your help on this.

<><
Livin' down on the cube farm. Left, left, then a right.