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?

Solution

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.