Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

conversion explanation requested Expand / Collapse
Author
Message
Posted Tuesday, March 26, 2013 6:00 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1435414
Posted Tuesday, March 26, 2013 6:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1435415
Posted Tuesday, March 26, 2013 6:22 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1435427
Posted Tuesday, March 26, 2013 6:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1435430
Posted Tuesday, March 26, 2013 7:51 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
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.
Post #1435487
Posted Tuesday, March 26, 2013 8:14 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:07 AM
Points: 12,903, Visits: 32,145
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1435509
Posted Tuesday, March 26, 2013 9:59 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 1:39 PM
Points: 245, Visits: 735
Thanks for your help on this.

<><
Livin' down on the cube farm. Left, left, then a right.
Post #1435594
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse