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

### Like this:

Like Loading...

Hi,

just read your actual blog entry and found some typo:

You wrote “DECIMAL(2,4)” – I think you mean “DECIMAL(4,2)”…

Rumtata

LikeLike

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.

LikeLike

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.

LikeLike

It should be DECIMAL(4,2) and DECIMAL(6,4).

First arg is total no.of digits and the second is the precision…

-Jyothi

LikeLike

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

LikeLike

Hi manish

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

LikeLike

yes Jyothi, you are correct

LikeLike

Good One

LikeLike

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

LikeLike

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.

LikeLike

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

LikeLiked by 1 person

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

LikeLike

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.

LikeLike

Hello Everyone…

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

Please advise

LikeLike

@JAM

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

LikeLike

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.

LikeLike

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

LikeLike

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

LikeLike

you are one man

LikeLike

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.

LikeLike

@naresh

See the nhelp file here: http://msdn.microsoft.com/en-us/library/ms173773.aspx

FLOAT(1-24) uses 4 bytes for storage.

FLOAT(25-53) uses 8 bytes for storage.

Therefore, the range is different.

LikeLike

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

LikeLike

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

LikeLike

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

LikeLike

I want to update decimal(don’t have scale) data with scale .

i.e., 45000 it shoul be convert like 450.00

any help ?

LikeLike

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

LikeLike

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

LikeLike

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

LikeLike

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

LikeLike

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.

LikeLike

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.

LikeLike

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.

LikeLike

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.

LikeLike

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

LikeLike

nice article to read and clear ur doubts

LikeLike

i want to knw exat difference between float and numeric

LikeLike

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

thanx

LikeLike

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

LikeLike

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

LikeLike

it worked..tnx

LikeLike

Hi pinal,

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

Thanks,

Ayyappan

LikeLike

Pinal Dave–

I love your blogs–I especially like how practical they are.

Keep writing!!!!!

LikeLike

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

LikeLike

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

Can anyone help?

LikeLike

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?

LikeLike

Post some sample data with expected result

LikeLike

These are some of the float values in a column:

0

2

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

LikeLike

Expected results:

0 = 0:00

2 = 2:00

8.3 = 8:30

LikeLike

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.

LikeLike

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

LikeLike

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

LikeLike

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

LikeLike

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

LikeLike

If you are doing it vai zizard, you need to enable this setting. Refer this fpr more informations

http://beyondrelational.com/blogs/madhivanan/archive/2011/06/06/exploring-ssms-enabling-save-changes.aspx

LikeLike

Dear madhivanan

This is working fine now, you saved my work.

Thanks a lot

LikeLike

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.

LikeLike

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.

LikeLike

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

LikeLike

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

LikeLike

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.

LikeLike

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′

LikeLike

Hi Madhivanan,

i have this error

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near ‘expiry’.

please help.

Thanks,

LikeLike

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,

LikeLike

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

LikeLike

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,

LikeLike

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?

LikeLike

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.

LikeLike

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

LikeLike

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

LikeLike

What does this value represent?

LikeLike

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

LikeLike

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

http://beyondrelational.com/modules/2/blogs/70/posts/14825/compare-numbers-stored-as-characters-seperated-by-space.aspx

LikeLike

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?

LikeLike

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 :)

LikeLike

You can create a column with decimal datatype

LikeLike

Hey how can I convert 0.0934 to 9.34 % and eventually round it off to 9 % .

Can anyone guide me please ?

LikeLike

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

LikeLike

Decimal ftvalue;

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

Eg: ftvalue = 123.123456789

dblrate = 123.123456

LikeLike

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

Why ????

LikeLike

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.

LikeLike

microsoft says it must be follow the rule that:

0 <= s <= p <= 38

LikeLike

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

LikeLike

hello sir,

What datatype is used?

float, decimal or numeric for a financial transaction…

thanks

LikeLike

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

LikeLike

What do you mean by data-type reane?

LikeLike

Pingback: SQL SERVER – Weekly Series – Memory Lane – #044 | Journey to SQL Authority with Pinal Dave

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

LikeLike

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

LikeLike

SET @num1=@num is where an implicit conversion happened and you lost precision. Refer https://msdn.microsoft.com/en-us/library/ms187928.aspx

LikeLike

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

LikeLike

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…

LikeLike