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

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.

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

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

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.

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′

Hi Madhivanan,

i have this error

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

Line 1: Incorrect syntax near ‘expiry’.

please help.

Thanks,

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,

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

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,

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?

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.

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

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