**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 comments. Leave new

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

What does this value represent?

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

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

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?

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

You can create a column with decimal datatype

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

Can anyone guide me please ?

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

Decimal ftvalue;

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

Eg: ftvalue = 123.123456789

dblrate = 123.123456

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

Why ????

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.

microsoft says it must be follow the rule that:

0 <= s <= p <= 38

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

hello sir,

What datatype is used?

float, decimal or numeric for a financial transaction…

thanks

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

What do you mean by data-type reane?

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

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

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

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

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…

In T-SQL, I have a table with Lat/Long stored in real data types. However they only show up at scale of 4 such as -110.8130 / 31.3580. When I convert the column data types to Decimal (18,8), all of Lat/Long become -110.81304932 / 31.35803986. Is there any way I can control the “display” precision/scale when looking at real data type?