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
93 Comments. Leave new
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
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
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
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
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.
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.
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.
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.
Thanks George. Just came across the question and could not figure it out myself.
nice article to read and clear ur doubts
i want to knw exat difference between float and numeric
suppose we have digits 56,by using this tel me the exact diff between float and numeric plzzzzzzzzzzz
thanx
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
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
it worked..tnx
Hi pinal,
does float datatype in sql server holds more the 40 scale?
Thanks,
Ayyappan
Pinal Dave–
I love your blogs–I especially like how practical they are.
Keep writing!!!!!
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
Using SQL Server 2005 i am trying to convert float values to datetime or time types.
Can anyone help?
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?
Post some sample data with expected result
These are some of the float values in a column:
0
2
8.3 — Which should equal 8:30 after converting to datetime
Expected results:
0 = 0:00
2 = 2:00
8.3 = 8:30
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.
select floor(10056.39+0.50),floor(10056.67+0.50)
SELECT CONVERT(INT,ROUND(10.56,0))
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
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
If you are doing it vai zizard, you need to enable this setting. Refer this fpr more informations
Dear madhivanan
This is working fine now, you saved my work.
Thanks a lot
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.