# SQL SERVER – Difference and Explanation among DECIMAL, FLOAT and NUMERIC

The basic difference between Decimal and Numeric :
They are the exactly same. Same thing different name.

The basic difference between Decimal/Numeric and Float :
Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly.
Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale.

Converting from Decimal or Numeric to float can cause some loss of precision. For the Decimal or Numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. DECIMAL(2,2) and DECIMAL(2,4) are different data types. This means that 11.22 and 11.2222 are different types though this is not the case for float. For FLOAT(6) 11.22 and 11.2222 are same data types.

Reference : Pinal Dave (http://blog.SQLAuthority.com), BOL DataTypes

## 91 thoughts on “SQL SERVER – Difference and Explanation among DECIMAL, FLOAT and NUMERIC”

1. Rumtata |

Hi,

just read your actual blog entry and found some typo:
You wrote “DECIMAL(2,4)” – I think you mean “DECIMAL(4,2)”…

Rumtata

Like

• Pranshul Gupta |

Hi Rumtata,
He wrote correct. why do you think that it should be DECIMAL(4,2)? He wrote DECIMAL(2,4) & that is correct. Because the corresponding value shown for that is 11.2222. Here precision is of 2 digits and scale is of 4.

Like

• Jaivs |

hello mr. prashant,
Precision can not be less than scale. Hence, its a typo error.
when you declare a datatype DECIMAL(2,4), you get below error.

The scale must be less than or equal to the precision.

Like

2. Jyothi |

It should be DECIMAL(4,2) and DECIMAL(6,4).
First arg is total no.of digits and the second is the precision…
-Jyothi

Like

• manish |

No, its correct, suppose I need to have values like .25

Like

• Ravi |

Hi manish
if you have values like .25 then you can use DECIMAL(2,2)

Like

• Ravi |

yes Jyothi, you are correct

Like

3. Akash |

Good One

Like

4. Jayasankar |

Hi . Some issues.

I have a table with a Real data type column and another one with float data type column.

table1 – real data type
table2 – float data type

insert into table1 values ( 22.22 )

insert into table2 select from table1

The value in table2 should have been 22.22 . But am getting some junk value in decimal part . Is this by design for SQL Server 2005 ?

Can anybody throw some light into this issue ? Thanks a lot for your time.

Regards,
Jay

Like

5. we have a table with fields listed as float (8). i notice though some of values are stored as 3.4 and others are stored as 3.3999999999. can you explain why a number, if input as 3.4 would be stored as 3.3999999999? thanks.

Like

6. Kray |

To be even more precise [pun unintended :)],
the first argument is ‘precision’, i.e. the number of significant digits, which includes both the digits to the left and to the right of the decimal point.
the second argument is the ‘scale’, i.e. the number of digits to the right of the decimal point.

So Rumtata and Jyothi are both correct about the DECIMAL(4,2) and DECIMAL(6,4).

Liked by 1 person

7. dat |

I just migrate my database from sql 2000 to sql 2005 and ran a test for writing data to dbf file (ms foxpro). The data in sql is ‘float’
for ex. 4000. but when this data is writen to dbf the format change to 4,000.00. this is a problem because now my dbf database see it as 4 instead of 4000. This is a major concern since we are dealing with inventory.

I’ve been looking in the net for a solution but haven’t came accross one yet.
Any suggestion on how I can fix this?

Thank you in advance for your help

Like

8. Mike Renwick |

Worth noting:- Numeric is appropriate in many places where money/currency is involved, but float is actually better for percentage columns….

Try this:-
Generate a table, called “Number” with 100 random numbers in it with 4 or more decimal places.

DECLARE @Total numeric(38,10)
SELECT @Total = SUM(Number) from Number

Select SUM(Percentage) FROM
(
SELECT Number / @Total as Percentage from
Number
) a

This doesn’t give 1.00 as it should…

But this does

Select SUM(Percentage) FROM
(
SELECT Convert(float,Number) / convert(float,@Total) as Percentage from
Number
) a

This is because although float doesn’t have perfect coverage across all values, it has much higher accuracy than numeric can manage.

Like

9. JAM |

Hello Everyone…

what is the best data type to store number of hours ex.(0.00) is it Numeric/Decimal/Float?

Please advise

Like

10. CodeShark |

@JAM

You can use datetime datatype for this, and if you are using sqlserver2008 you have time datattype also.Thanks

Like

11. Tejal Rupera |

Hello Everyone,

I have one column with decimal(18,3) and column contains data like 1.050.

Now at the retrieval time i want data like 1.05 (omit extra 0’s from floating point)

Please help me in this.

Like

12. Syed |

Hi All
i am selecting a numric value (1260179004) through cursor and storing in a variable, when i am reading varaible then i am getting out put in Eponant ie 1.2 E009
, please can i get some idea to get numric values as it is stored in tables

Like

13. Imran Mohammed |

@Syed

Try converting that values to decimal (38,2) and see if that works…

If you use numeric or real data types, you see those kind of outputs.

~ IM.

Like

14. jim bose |

you are one man

Like

15. naresh |

hi Pinal,
Can you tell me what is the difference between float(24) and float(53) or in another words real and float.

as real is float(24) and by default float is float(54)

I am running this sql code in SQL 2005
–=================================
declare @a as float(24)
set @a=0.85
select cast ( @a as float(53))
–=================================

and the result is

0.850000023841858

Thanks.

Like

16. Srini |

Hi,

I have a table with 2 million rows. There are four columns of data type decimal(19,6). The size of the table comes to around 250 MB ( in compressed mode) I created a new table with the columns as float data type and migrated the data. I compressed the table. The size of the table is now around 400 MB. Why is it this way

Like

17. Hello Srini,

The size of table could increase because of new data type or because of Fillfactor. The Decimal value with scale upto 9 stores in 5 bytes while float could be taking 8 bytes.

Regards,
Pinal Dave

Like

18. Peter Frissen |

Hi, I’m still strugling with multiplying float with numbers as in example below. The first resultset is in type real, the second in type float. In this example my price = 22.23 and the factor 100000. In the real-example the result is correct, but in the float-example I would expect that the result would be 2223000, but it is 2222999.95422363, rounded to 2 decimals it’s 2222999.95. And that’s not correct. Any ideas how to solve this problem? Should I round to 2 or 4 decimals first and then multiply? Is there a general solution?

Thx.

declare @tReal table

(col1 real)

declare @tFloat table

(col1 float)

declare @Factor int

select @Factor = 100000

insert into @tReal values (22.23)

insert into @tFloat select col1 from @tReal

select col1 as Contents,

col1 * @Factor as MultiplyFactor

from @tReal

select col1 as Contents,

col1 * @Factor as MultiplyFactor

from @tFloat

Like

19. Taduri Sreedhar |

I want to update decimal(don’t have scale) data with scale .
i.e., 45000 it shoul be convert like 450.00
any help ?

Like

20. Hello Taduri,

The example you used here (45000 to 450.00) is not a data type conversion but a value change derived from devide by 100.
You can convert a decimal like 45000 to 45000.00 by using below syntax:

CONVERT(DECIMAL (10,2), yourvalue)

Regards,
Pinal Dave

Like

21. Pinal, as pointed out at the first two comments decimal(2,4) should be decimal(4,2) as you cant have scale more than that of precision

Like

22. Hi,

In my one project I am facing very strange problem of float.
Using SQL query I am performing average of one float column using group by category column. If category have single brand then it will not produce same average.

e.g. 7 will produce 7.0000000000000009

Please help on this.

Thanks in advance

Regards,
Vishal Jani

Like

23. Tom Belanger |

Hi,

I have the same problem here. If I do “select cast(‘0.35′ as float)”, it gives me 0.34999999999999998 but only on 1 server out of 100. I realy don’t know what is the problem on that server.

regards
Tom

Like

24. Pavani |

Hi Pinal,
I am having one float value in the database (Sql server 2008). In my form, I have two text boxes where first text box accepts the number of precision points and the second text box accepts the decimal number. If first text box is having value as ‘2’ then second text box will accept the decimal number with 2 points precision(Ex.10.24). Now the problem is when I am saving this value in the database, it is adding some extra precisions to that floating point value like 10.2456098. I have to either save it exactly 10.24 in the database or while retreiving I should get it as 10.24. Can you tell me how to solve this problem? Please Help me.

Thanks & Regards,
Pavani.

Like

• Marko Parkkola |

There is no solving this. Floats (and other floating point numbers) are “inaccurate” by nature. This is why you should never store, for example, money values to float data types.

You could try to use DECIMAL(18, 2) datatype instead of float. “18, 2″ means that you can have 0-18 digits (on either side of the decimal separator) with 0-2 decimals, so “10.24” goes nicely to this data type.

Btw. I just changed about 20 FLOAT types to DECIMAL(18,3) types in the DB/tables and went through ~80 stored procs and functions this morning and changed them to use correct data type. All the weight measurements were stored in floats and our billing is based on weight on some products so, well, customers do like to have accurate bills :)

But I just can’t press this enough: If you don’t need floating point variables, and you most probably very rarely do, do not use them. Instead use fixed point variables like DECIMAL.

Like

25. haansi |

Hello,

I am doing this small exercise.

declare @No decimal(38,5);
set @No=12345678910111213.14151;

select @No*1000/1000,@No/1000*1000,@No;

Results are:

12345678910111213.141510
12345678910111213.141000
12345678910111213.14151

Why results of first 2 selects are different when mathematically it should be same.

Why it is plz guide on this.

Like

• George |

Did you ever receive a reply on this? Your problem is with your precision. there are five digits of precision. When you first divide by 1,000, you are moving the decimal point three positions to the left. Then, working back five spots from this new position, you only have two of your original decimal points left for precision. This would work correctly if you had decimal(38,8).

12345678910111213.14151 / 1000 ==> 12345678910111.213141
when you multiply that by your 1000 again, the decimal moves those three spots back to the right.

Like

• Samir |

Thanks George. Just came across the question and could not figure it out myself.

Like

26. Maddy..... |

nice article to read and clear ur doubts

Like

27. keerti |

i want to knw exat difference between float and numeric

Like

28. keerti |

suppose we have digits 56,by using this tel me the exact diff between float and numeric plzzzzzzzzzzz

thanx

Like

29. hello budy

i have a trouble for following example.

input = 12.56333333 and want as out put 12.57

does there any solution about this please send me.

i have implemented this with multiple line of code but i thing this is wrong so please send me

” only single line snippet ”

thanks in advance

from

kanthavadiya Amrut

Like

• I guess the following single line query will help you. replace the numeric constant with variables.

select cast((12.56333333 * 100 +1) / 100 as decimal(18,2))

let me know if you have additional help.

Cheers, Ayyappan

Like

• Kumar |

it worked..tnx

Like

30. Hi pinal,

does float datatype in sql server holds more the 40 scale?

Thanks,
Ayyappan

Like

31. Pinal Dave–
I love your blogs–I especially like how practical they are.
Keep writing!!!!!

Like

32. Hi

I have one problem..please give me solution..it’s urgent

DECLARE @data real
SET @data =9.660606E+07
declare @value varchar
set @value = cast (cast(@data as decimal) as varchar)
select @value
SELECT
CASE
WHEN CHARINDEX(‘E’, @data) > 0 THEN CAST(LEFT(@data,
CHARINDEX(‘E’, @data) – 1) AS FLOAT) * POWER(10.0, CAST(RIGHT(@data,
CHARINDEX(‘E’, REVERSE(@data)) – 1) AS INT))
ELSE CAST(@data AS BIGINT)
END
run this in sqlserver

actual value 9.660606E+07 is 96606060
but return 96606100
i want exact value 96606060
if you take decimal type instead of real..then run fine but

The value from database comes is real type

Like

33. Jag |

Using SQL Server 2005 i am trying to convert float values to datetime or time types.

Can anyone help?

Like

34. Jag |

My last comment had the wrong email address.

Using SQL Server 2005 i am trying to convert float values to datetime or time types.

Can anyone help?

Like

• Jag |

These are some of the float values in a column:

0
2
8.3 — Which should equal 8:30 after converting to datetime

Like

35. Jag |

Expected results:

0 = 0:00
2 = 2:00
8.3 = 8:30

Like

36. I have values in my table 13123.84,10056.39,11441.98

know my condition if decimal less than .50 then 10056 if greater than .50 then 10057. in sql server 2000. how i can get this results?

please help me.

Like

• select floor(10056.39+0.50),floor(10056.67+0.50)

Like

• Ramees |

SELECT CONVERT(INT,ROUND(10.56,0))

Like

37. rakesh |

First argument should be total no.of digits and the second one is precision.
i.e decimal(3,2) allows 1.12 and decimal(2,1) allows 1.1

Like

38. Hitesh |

Hi

I have one problem while altering column datatype from Numeric to Integer. When I tried to alter it then its prompted with message “You should have to re-create the table to change etc…”. Also, earlier my database was running on sql server 2005 and now it is running on 2008.

Please help me to solve this problem

Thanks

Like

39. bala |

Hi, i have problem with rounding float data types. I am not getting rounded value correctly. For example when i run this,

declare
@vtest1 float(53),
@vtest2 float(53)
begin
SET @vtest1 = 4551.935
SET @vtest2 = 759.935

SET @vtest1 = ROUND(@vtest1,2)
SET @vtest2 = ROUND(@vtest2,2)

print replace(rtrim(replace(ltrim(str(Cast(@vtest1 as float),25, 8)),’0′,’ ‘)),’ ‘,’0′)
print replace(rtrim(replace(ltrim(str(Cast(@vtest2 as float),25, 8)),’0′,’ ‘)),’ ‘,’0′)
end

Output is

4551.94
759.93

Can anyone explain me why there is difference in decimal (.93 vs .94). In both case i am expecting .94. How i can achieve this without changing float to numeric or decimal?

Thanks.

Like

40. venkatesh |

The numeric data type can store a maximum of 38 digits, all of which can be to the right of the decimal point. The numeric data type stores an exact representation of the number; there is no approximation of the stored value.

Like

41. brylle |

Hi, i’m creating a trigger that checks the sum of a column if it is equal to 1.

here’s my query:

DECLARE @subtotal as numeric(19,5)
SET @subtotal = 0.00000

SELECT @subtotal = @subtotal+(SELECT CAST(ROUND(SUM(BaseQty),5) as NUMERIC(19,5)) FROM INSERTED WHERE Table1.ID = INSERTED.ID) FROM Table1 WHERE Table1.ID IN (SELECT DISTINCT ID FROM INSERTED)

IF (@subtotal > 1.00000)
BEGIN
RAISERROR (‘Formulation is not equal to 1..’, 16, 10)
END

if the subtotal returns 1.4 and higher the result is true, but if subtotal is equal to 1.3, 1.2, 1.1 it returns false..

can anybody tell me whats wrong with the query.?

Thanks..

Like

42. Comparison of floating point values in SQL Server not always gives the expected result.

With this function, comparison is done only on the first 15 significant digits. Since SQL Server only garantees a precision of 15 digits for float datatypes, this is expected to be secure.

The function expects two inputs of default type float, that’s an implicit float(53)

See http://floatequalsfloat.codeplex.com for the function

Like

43. Khan |

I HAVE THE BELOW SCRIPT

select DISTINCT expiry from Rec_Dtl where grn_number =27964 and m_code =’M605′

THE RERSUTLS ARE

exiry
——-
382268; 07/02/05
976131, 31/05/2011

I WANT THE SEPRATE TWO VALUES LIKE BELOW.

DISTINCT EXPIRY 1 , EXPRIY 2
————————————-
382268 07/02/05
976131 31/05/2011

I WANT RESULTS EXACTLY LIKE ABOVE. PLEASE HELP ME.

Like

• Split it using substring

select substring(expiry,1,charindex(‘;’,expiry)-1),substring(expiry,charindex(‘;’,expiry),len(expiry)) from Rec_Dtl where grn_number =27964 and m_code =’M605′

Like

44. Khan |

Hi Madhivanan,

i have this error

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ‘expiry’.

please help.

Thanks,

Like

45. Khan |

HI madhivan,

i adjust the script whic you’ve given. like below

select distinct substring(expiry,1,charindex(‘;’,expiry)-1),substring(expiry,charindex(‘;’,expiry),len(expiry))
from Rec_Dtl where grn_number =27964 and m_code =’M605′

but know i have this error

Server: Msg 536, Level 16, State 3, Line 1
Invalid length parameter passed to the substring function.

Thanks,

Like

• Do all values have ; as part of it? If not you may need to use different logic. Replace all comma into semicolon before running the code

select substring(expiry,1,charindex(‘;’,expiry)-1),substring(expiry,charindex(‘;’,expiry),len(expiry))
from (select distinct replace(expiry,’,’,';’)) as expiry from Rec_Dtl where grn_number =27964 and m_code =’M605′) as t

Like

46. Khan |

Hi madhivanan,

the below scripts works well.

select distinct substring(expiry,1,charindex(‘,’,expiry)-1),substring(expiry,charindex(‘,’,expiry),len(expiry))
from Rec_Dtl where grn_number =27964 and m_code =’M605′

and results.

—————————— ——————————
#3453453 ,\$21-12-2014
#S4345 ,\$4YRS

but i don’t want comma infront of 2nd column, like ,\$21-12-2014 i need only \$21-12-2014 without comma

Thanks a lot for your support. May i have your email id please.

Regards,

Like

47. Bryan Duchesne |

I have a SQL procedure using OPENROWSET to import MS Foxpro data into a SQL table. When I run the procedure, I am getting errors on all my numeric field in the Foxpro table. What appears to be happening is if the DBF integer field is defined as 6,0 and contains 6 digits, it errors out. If less than 6 digits, it is fine. I thought I read somewhere that this is caused by a difference in the way that SQL treats numeric data over the way Foxpro does. Aside from physically changing the data definition in Foxpro (which I cannot do as the data I am importing is from a 3rd party program), is there any way to fix this problem?

Like

48. I have a question. I am new to .net platform. I am designing a database with sql 2008 and vb 2010. I want to create staff numbers that will be 5 digits and divisible by 7. I would have loved to make this primary key and auto increment. how do I do this? PLS HELP ME.

Like

49. Abhimanyu |

If we want field of the table use sp_help in sql server query window.

if you want any procedure or want to see what is in particular store procedure than short cut for this is sp_helptext

Like

50. MS |

Need help in converting float “3538135420.163” to datetime. PLEASE HELP!

Like

51. Jyoshna |

I have a value as 1533.1.1 i have to add +1 for this?
and aslo 1533.1.1.1 i have to add +1
Result will be like 1533.1.2,1533.1.1.2.
How to write a query in sqlserver

Like

52. Ashwin A |

I want to create a table with a variable accepting decimal numbers that has the decimal part like ( .53 or .23689) . How will i do this???
Thanks in advance :)

Like

53. ny_giants_12 |

Hey how can I convert 0.0934 to 9.34 % and eventually round it off to 9 % .
Can anyone guide me please ?

Like

• SQL12 |

* 100 and then round the reasult will give you right number

Like

54. SubhaN |

Decimal ftvalue;
double dblRate = Math.Round(Convert.ToDouble(ftvalue), 6);

Eg: ftvalue = 123.123456789

dblrate = 123.123456

Like

55. Converting from decimal or numeric to float or real can cause some loss of precision

Why ????

Like

56. John Knight |

Hi,

Why can’t a variable be used to create the scale when using cast as numeric?:

declare @Scale as integer

set @Scale = 2
select cast(234.56789 as numeric(5, @Scale)

This produces an error.

Yet if using Round the variable works:

declare @Scale as integer
set @Scale = 2
select ROUND(234.56789,@Scale)

Any ideas? Our decimal precision varies according to a value elsewhere in the database.

Like

57. SonDurak |

microsoft says it must be follow the rule that:
0 <= s <= p <= 38

Like

• Aditya Suley |

Hi,
I need an urgent help on conversion.
I am doing BCP in a value 1,698,920.971 in a column of float datatype having length 8 and Precession 53 and Scale Null.

But when i see the imported value in Database i see it like 1,698,920.97 but it should look like 1,698,920.971. Any Idea???????

Like

58. shubham |

hello sir,
What datatype is used?
float, decimal or numeric for a financial transaction…
thanks

Like

59. CoolArian |

Getting the following error, please advice. ValidStartDates is a table containing datetime objects in 2 columns i.e StartDate and EndDate
i.e.
StartDate EndDate
8/12/2009 12:00:00.000 AM | 8/19/2009 12:00:00.000 AM
.
.
.
.
12/12/2012 12:00:00.000 AM | 12/19/2012 12:00:00.000 AM

[Microsoft][ODBC SQL Server Driver][SQL Server]Arithmetic overflow error converting expression to data type float.(22003,8115)

drop table Factorials
go

with Dates(max_samples) as (SELECT count(*) from ValidStartDates),
t(x, factorial) as (
select 0, cast(1.0 as float)
union all
select x+1, factorial*(x+1)
from t, Dates where x <= max_samples)
select isnull(x, 0.0) as x, factorial
into Factorials
from t
option (maxrecursion 300)
go

Like

60. Rebecca |

What do you mean by data-type reane?

Like

61. Nice blog.very interesting topic, decimal float and numeric.

Like

62. ashish |

DECLARE @num float , @num1 Dec(19,2)
SET @num=950000.64
SET @num1=@num
SELECT CAST(@num1 as VARCHAR(18)),CAST(@num as VARCHAR(18)),@num

ideally all 3 should have same value. But I am getting different values..

Like

63. Ashish |

Even without using @num1 = @num, same reult is coming….

DECLARE @num float , @num1 Dec(19,2)
SET @num=950000.64
SET @num1=950000.64
SELECT CAST(@num1 as VARCHAR(18)),CAST(@num as VARCHAR(18)),@num

Like

64. Ashish |

I also observed that LEN() function is behaving differently for float & decimal data types..
Example:
declare @a float, @b float, @c decimal(19,2)
select @a = 350470.88, @b = 35070.88, @c = 350470.88
select @a A, @b B, @c C, LEN(@a) len_A, LEN(@b) len_B, LEN(@c) len_C

Ideally len_A & len_C should have same value…

Like