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