Results 1 to 7 of 7
Thread: SQL server decimals

081012, 04:02 #1Registered User
 Join Date
 Feb 2009
 Posts
 51
Unanswered: SQL server decimals
A numeric value of Decimal(16,2) cannot be assigned to Decimal(16,4).
How to get a correct precision of a value (@V1) derived from two arbitrary decimal numbers (@X,@Y) with unknown precision and scale in to a decimal type variable.
declare @V1 as @decimal
set @V1 = @X*@Y
Will @V1 be having 4 decimals?.......
She drank beer with Coke Cola
and that's the way I like it...
.......

081012, 08:50 #2Registered User
 Join Date
 Apr 2012
 Posts
 213
I think that for 4 decimals should use the Round function. Ex:
Code:set @V1 = ROUND(@X * @Y, 4);

081012, 10:14 #3Registered User
 Join Date
 Jan 2003
 Location
 Massachusetts
 Posts
 5,819
Provided Answers: 14The variable or column that contains your result would have to be decimal (18, 4). The two numbers (Precision and scale) define 1 How many digits you allow, and 2 how many decimal places there are. Taking a smaller sample:
Code:create table #temp (col1 decimal (5, 4)) insert into #temp values (10)
This, however does work:
Code:create table #temp (col1 decimal (5, 4)) declare @in1 decimal (5, 2), @in2 decimal (5, 2) set @in1 = 1.00 set @in2 = 3.00 insert into #temp values (@in1/@in2) select * from #temp

082212, 20:34 #4Registered User
 Join Date
 Feb 2009
 Posts
 51
appreciate input so far
I think I ought to be clear a scalar function that return a decimal value and also round this decimal value accordingly by looking at another variable which defines number of decimals and so it will be:
1. FUNCTION X
2. RETURNS DECIMAL(30,8)
3. DECLARE @DECIMALS AS INT  will have values 0 or 2 or 4 or 6
4. DECLARE @VALUE1 AS DECIMAL(18,6)
5. DECLARE @VALUE2 AS DECIMAL(18,2)
6. RETURN ROUND(@VALUE1* @VALUE2, @DECIMALS) assume @decimals = 2
What will be the precision and scale of the constant as a result of multiplication?
What will be the return value precision and scale?
Will the above function work? if not how to fix it?
take a punt guys
side note:
What prevent MS SQL from making DECIMAL type similar to the ones that we used in programming languages?
Say we have a decimal variable created and assign a decimal value to it with a precision and a scale it will retain it.
But MS SQL doing this for constants.
So we can calculate and return decimals without knowing precision and scale.
Last edited by dbfHelp; 082212 at 20:41.
.......
She drank beer with Coke Cola
and that's the way I like it...
.......

082312, 12:18 #5Registered User
 Join Date
 Aug 2012
 Posts
 45
Have you tried it yourself?
Code:DECLARE @Dec_18_2 DECIMAL(18,2), @Dec_18_6 DECIMAL(18,6) SELECT @Dec_18_2 = 10.12 SELECT @Dec_18_6 = 10.123456 SELECT @Dec_18_2 SELECT @Dec_18_6 SELECT @Dec_18_2 * @Dec_18_6
What will be the return value precision and scale?
Converting decimal and numeric Data
For the decimal and numeric data types, SQL Server considers each specific combination of precision and scale as a different data type. For example, decimal(5,5) and decimal(5,0) are considered different data types.
In TransactSQL statements, a constant with a decimal point is automatically converted into a numeric data value, using the minimum precision and scale necessary. For example, the constant 12.345 is converted into a numeric value with a precision of 5 and a scale of 3.
Will the above function work? if not how to fix it?
Code:CREATE FUNCTION fnDecimalTest (@Value1 DECIMAL(18,2), @Value2 DECIMAL(18,6)) RETURNS DECIMAL(30,8) AS BEGIN DECLARE @Return DECIMAL(30,8) SELECT @Return = @Value1 * @Value2 RETURN @Return END

082412, 23:10 #6Registered User
 Join Date
 Feb 2009
 Posts
 51
thanks for your comments
sgnidow
Appreciate your comments on this.
still got to see what others have to say.
There is another question in my comment about DECIMAL variables in MS SQL Server. If you assign a numeric value with a decimal part to a variable of type DECIMAL(not given precision and scale) then decimal part will be dropped (i). but a decimal constant will retain the decimal part(ii). What reasons behind MSSQL server that not retain the decimal part in (i) similar to in(ii)?.......
She drank beer with Coke Cola
and that's the way I like it...
.......

082512, 09:44 #7Registered User
 Join Date
 Aug 2012
 Posts
 45
Ok, I get this part, and this should demonstrate...
Code:DECLARE @Dec_no_precision DECIMAL SELECT @Dec_no_precision = 10.123 SELECT @Dec_no_precision
but a decimal constant will retain the decimal part(ii).
Code:IF OBJECT_ID('tempdb..#dec','u') IS NOT NULL DROP TABLE #dec CREATE TABLE #dec ( dec_no_precision DECIMAL ) INSERT INTO #dec VALUES (10.123) SELECT dec_no_precision FROM #dec
What reasons behind MSSQL server that not retain the decimal part in (i) similar to in(ii)?
Greg