Converting float to decimal in SQL Server 2008

Question

I have a view which needs to return type decimal for columns stored as float.

I can cast each column to decimal as follows:

, CAST(Field1 as decimal) Field1

The problem with this approach, is that decimal defaults to 18,0, which automatically rounds the float columns to 0. I would like to keep a precision of up to 12 decimal places.

However, if I do this:

, CAST(Field1 as decimal(12,12)) Field1

I get a runtime error:

"Arithmetic overflow error converting float to data type numeric"

the float column is defined as length: 8 Precision: 53 in the table. I can not modify anything about the table.

What's the proper way to cast it as decimal w/out losing decimal precision?

My suggestion is that you stop storing data as float as it is an inexact data type and you will get rounding errors if you do calculations on it. — HLGEM
– HLGEM, Commented Nov 15, 2010 at 18:04

Quassnoi · Accepted Answer · 2010-11-15 16:57:19Z

35

12, 12 means no digits before the decimal separator: 12 digits in total, 12 of them being after the period.

Use a more appropriate range, say:

DECLARE @var FLOAT = 100
SELECT  CAST(@var as decimal(20,12))

which gives you 8 digits before the separator, or adjust it as needed.

answered Nov 15, 2010 at 16:57

Quassnoi

427k94 gold badges628 silver badges623 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Sonic Soul Over a year ago

great thanks, do you know if there is a way to format it so it only returns decimal places if there is a value? if i select from the table, it does not show the trailing 0's, but after the cast the 12 zeros ALWAYS show..

Quassnoi Over a year ago

@Sonic: so what you need: to return a DECIMAL or format? Formatting will leave you with a VARCHAR.

Vallier Over a year ago

to go along with this answer check out this link which explains it in great detail: learn.microsoft.com/en-us/sql/t-sql/data-types/…

Collectives™ on Stack Overflow

Converting float to decimal in SQL Server 2008

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related