Geek asks and answers

Paste in decimal with given number of decimal places

Is there a way to make a number into a decimal number with a given number of decimal places? I tried:

SELECT ...
       CAST(NumericField AS NUMERIC(15, @DecimalPlaces) AS NumericField,
       ...

But that did not work.

EDIT : I made a mistake and wrote NUMBERinstead NUMERIC. But the question stands still: how can I convey a value NUMERICwith a specified number of decimal places?

+3
source share
5 answers
declare @decimal int=5
declare @decimalNum float =8931.0380106023125083

select ROUND(@decimalNum, @decimal,1)

For trailing zeros, use this:

declare @decimal int=5
declare @decimalNum float =8931.12

select STR(@decimalNum, 25, @decimal)

Note that the above selection will return a varchar type, not a decimal, numeric, floating, or any other type.

+2
source 

declare @Value float = 123.4567, @RoundTo int = 2
select round(@Value * power(10, @RoundTo), 0) / power(10, @RoundTo)
Run codeHide result
+2
source

.

cast (decimal value (10.2)) 10 is the total number, including decimal numbers, and 2 is the number of decimal places

+1
source

One of the methods...

WITH T(NumericField, DecimalPlaces) AS
(
SELECT 1.234,10 UNION ALL
SELECT 1.234,3 
)
SELECT CASE DecimalPlaces 
        WHEN 15 THEN CAST(NumericField AS NUMERIC(30, 15))
        WHEN 14 THEN CAST(NumericField AS NUMERIC(30, 14))
        WHEN 13 THEN CAST(NumericField AS NUMERIC(30, 13))
        WHEN 12 THEN CAST(NumericField AS NUMERIC(30, 12))
        WHEN 11 THEN CAST(NumericField AS NUMERIC(30, 11))
        WHEN 10 THEN CAST(NumericField AS NUMERIC(30, 10))
        WHEN 09 THEN CAST(NumericField AS NUMERIC(30, 9))
        WHEN 08 THEN CAST(NumericField AS NUMERIC(30, 8))
        WHEN 07 THEN CAST(NumericField AS NUMERIC(30, 7))
        WHEN 06 THEN CAST(NumericField AS NUMERIC(30, 6))
        WHEN 05 THEN CAST(NumericField AS NUMERIC(30, 5))
        WHEN 04 THEN CAST(NumericField AS NUMERIC(30, 4))
        WHEN 03 THEN CAST(NumericField AS NUMERIC(30, 3))
        WHEN 02 THEN CAST(NumericField AS NUMERIC(30, 2))
        WHEN 01 THEN CAST(NumericField AS NUMERIC(30, 1))
        WHEN 00 THEN CAST(NumericField AS NUMERIC(30, 0))
        ELSE CAST(NULL AS SQL_VARIANT)
        END
FROM T
+1
source

perhaps you can try the following: DECLARE @quantityPrecision INT = 5; set @quantityPrecision = your value update table A set quantity = round (quantity, @amountPrecision) where XXX

+1
source

More articles:


All Articles