Migration conversion of float data to decimal data

Question

I am migrating data from one table to a new table. The old table uses FLOAT, and in the new table I am using DECIMAL as the field attribute.

I am using the following statement which worked fine: CAST(OLD_COLUMN_NAME as DECIMAL(9,2) AS 'NEW_COLUMN_NAME'

that works like a charm until I hit the bump in the road. The old data is defined as float,null, the new field is defined as decimal(5,5). I understand that decimal(5,5) will requires all data behind the decimal for 5 positions. Just wondering if they is any way to handle this problem of moving data from a float data field to a decimal data field.

The input data from the old field is varied and looks like this: 5, 0.5, 0.5, 0.75, 2, and so forth.

The error I am receiving is:

Msg 8115, Level 16, State 6, Line 8 Arithmetic overflow error converting float to data type numeric.The statement has been terminated.

The code is against a SQL SERVER 2005 database using SQL SERVER 2008. Not sure this matters, but thought I would include this information.

Could someone shed some light on how to address this data conversion issue? Thank you!

Answer 1

I would recommend doing something like this.

Cast(Round(field, 5) As Decimal(5, 5))

Answer 2

Decimal(5, 5) only has a range - 0.99999 to + 0.99999

but you say you are trying to put 5 and 2 into it. Do you need Decimal(10, 5)?

If not you have indeed been left a bit of a conundrum.

I mention the following as a point of interest rather than a serious suggestion, though, who knows, it may be useful!

If the column is nullable one way to fullfill your bosses requirements would be

create table #t (j int,i Decimal(5, 5)  null)

set ansi_warnings off
set arithabort off

insert into #t values(1,0.2345678)
insert into #t values(2,10)
insert into #t values(3,0.455464)

select * from #t

Output

Arithmetic overflow occurred.
j           i
----------- ------------------------------
1           0.23457
2           NULL
3           0.45546

Answer 3

Does your boss understand that decimal(5,5) won't allow anything to the left of the decimal point?

Ask your boss what he/she wants in plain english. If your boss is really asking you to store numbers greater than .99999, but insisting specifically on "decimal(5,5)", then clearly they don't understand what they're asking for. The first "5" in decimal(5,5) refers to the total number of digits in the number (before and after combined)