Results 1 to 7 of 7
  1. #1
    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...
    .......

  2. #2
    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);
    Hope this helps.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The 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)
    fails miserably, because the datatype does not allow for a 10's column.

    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

  4. #4
    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; 08-22-12 at 20:41.
    .......
    She drank beer with Coke Cola
    and that's the way I like it...
    .......

  5. #5
    Join Date
    Aug 2012
    Posts
    45
    Quote Originally Posted by dbfHelp View Post
    What will be the precision and scale of the constant as a result of multiplication?
    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?
    It looks like you are going to tell your function to return DECIMAL(30,8), so that is the precision and scale. If you are going to round anyway, then why not return DECIMAL(38,18)? This is what books online says...
    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 Transact-SQL 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?
    Have you tried it as is? Does it do what you want it to do? You may want to be telling us if it works or not. Are you just looking for function syntax help?
    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
    That is without rounding, so you can see what its returning

  6. #6
    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 MS-SQL 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...
    .......

  7. #7
    Join Date
    Aug 2012
    Posts
    45
    Quote Originally Posted by dbfHelp View Post
    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).
    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).
    I'm not sure I get what you are asking here. By constant do you mean a value in a table that is defined as DECIMAL with no precision? If so, you will see the same behavior...

    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 MS-SQL server that not retain the decimal part in (i) similar to in(ii)?
    I can't answer that, but you could read every word in the DECIMAL and NUMERIC data type documentation in BOL, and you might find your answer. I do know one thing, and that is by explicitly declaring the scale and precision you don't have to worry about what SQL Server will do if you don't declare it. Could you give more detail about what you are trying to do? Is it that you don't like to see all the extra zero's at the end of your result?

    Greg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •