Copy values from real value column to decimal value column in SQL
In one of our SQL tables, we have a real field. The accuracy of the real field is 7. We need higher accuracy in our application. Therefore, we decided to convert our real field to a decimal field.
Since our table is very large and since we cannot turn off the transaction logs, we decided to use the following approach.
- Rename the real column to real_value_old (Previous name was real_value)
- Create a new real column named real_value and data type decimal (38, 8)
- Copy values from real_value_old to real_value in batches
We run into some problems with this approach. For example, if real_value_old is 545370.2, real_value is set to 545370.18750000
When we checked the problem, we realized that it was due to the following problem
update table set real_value = cast (real_value_old as decimal(38,8)) where some_condition
update table set real_value = ROUND(cast (real_value_old as decimal(38,8)), 8) where some_condition
update table set real_value = convert (decimal(38,8), real_value_old, 38) where some_condition
update table set real_value = real_value_old where some_condition
In all the above cases, if real_value_old is 545370.2, real_value is set to 545370.18750000
Can anyone provide a solution here?
This code quickly shows what the problem is. Confirmed on sql server 2012
DECLARE @real REAL = 545370.2
SELECT cast(@real AS DECIMAL(38, 8))
,ROUND(cast(@real AS DECIMAL(38, 8)), 8)
,convert(DECIMAL(38, 8), @real, 38)
source to share
Below link will give a function to get number of digits for floating point datatype
How to get number of digits after decimal point in float column in ms sql?
Change this function to use REAL in function argument and then, below sql will work
update table set real_value = ROUND(CAST (real_value_old AS decimal (38,8)), dbo.countDigits(real_value_old)) where some_condition
The complete code is given below
CREATE FUNCTION dbo.countDigits(@A real) RETURNS tinyint AS
BEGIN
declare @R tinyint
IF @A IS NULL
RETURN NULL
set @R = 0
while @A - str(@A, 18 + @R, @r) <> 0
begin
SET @R = @R + 1
end
RETURN @R
END
GO
DECLARE @real REAL = 545370.2
SELECT ROUND( cast(@real AS DECIMAL(38, 8)), dbo.countDigits(@real))
source to share
Definition of REAL datatype from MSDN documentation:
Approximate number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the range of data types can be represented accurately. ISO synonym for real is float (24).
This example shows how it works in the real world (pun intented)
DECLARE @real REAL = 545370.2
SELECT cast(@real AS DECIMAL(38, 8))
,ROUND(cast(@real AS DECIMAL(38, 8)), 8)
,convert(DECIMAL(38, 8), @real, 38)
Each of these results in 545370.18750000
DECLARE @float float(24) = 545370.2
SELECT cast(@float AS DECIMAL(38, 8))
,ROUND(cast(@float AS DECIMAL(38, 8)), 8)
,convert(DECIMAL(38, 8), @float, 38)
Same as first block
DECLARE @doublefloat float(53) = 545370.2
SELECT cast(@doublefloat AS DECIMAL(38, 8))
,ROUND(cast(@doublefloat AS DECIMAL(38, 8)), 8)
,convert(DECIMAL(38, 8), @doublefloat, 38)
Double precision float is entitled: 545370.20000000
So there you go. Do not use aproximate numbers when the results cannot be aproximated and examine your data types to avoid headaches;)
EDIT: The specific number given in the example can be handled like this:
DECLARE @float real = 545370.2
SELECT CEILING(@float*10)/10
Depending on the range of numbers you are handling, you can look at the formula using CEILING / FLOOR / ROUND which works for the entire range.
source to share