# 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 (https://blog.sqlauthority.com), BOL DataTypes

#### Related Posts

• venkatesh
July 20, 2011 12:46 pm

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.

• brylle
August 1, 2011 5:31 pm

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..

• Heber Dijks
October 21, 2011 9:01 pm

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 for the function

• Khan
January 2, 2012 6:36 pm

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

• January 4, 2012 1:42 pm

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′

• Khan
January 4, 2012 1:47 pm

i have this error

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

Thanks,

• Khan
January 4, 2012 2:56 pm

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,

• January 4, 2012 3:09 pm

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

• Khan
January 4, 2012 5:07 pm

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

Regards,

• Bryan Duchesne
January 22, 2012 6:35 pm

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?

• Emmanuel Damisa
March 10, 2012 12:49 pm

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.

• Abhimanyu
April 13, 2012 4:18 pm

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

• MS
May 15, 2012 8:38 pm

• May 23, 2012 4:29 pm

What does this value represent?

• Jyoshna
May 22, 2012 11:07 am

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

• May 23, 2012 3:24 pm

Why do you want tto store numbers this way? It is difficult for doing arithmetic calculations. You can use this technique

• Jyoshna
May 23, 2012 5:01 pm

Actually its a variable number, according to our requirement we will store data accordingly. in that particular decimal value i have to find last number and have to add +1 to that?

• Ashwin A
June 12, 2012 11:33 am

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???

• June 18, 2012 2:44 pm

You can create a column with decimal datatype

• ny_giants_12
July 17, 2012 9:37 am

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

• SQL12
August 29, 2012 9:48 pm

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

• SubhaN
September 7, 2012 12:25 pm

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

Eg: ftvalue = 123.123456789

dblrate = 123.123456

• Parvej Solkar
September 20, 2012 2:47 pm

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

Why ????

• John Knight
September 28, 2012 7:36 pm

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.

• SonDurak
November 8, 2012 5:57 pm

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

• January 4, 2013 2:54 pm

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???????

• shubham
February 6, 2013 9:45 pm

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

• CoolArian
February 21, 2013 3:13 pm

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