Dynamic decimal precision

Question

Dynamic decimal precision

stelianx

SSChasing Mays

Points: 604
More actions
May 6, 2009 at 6:41 am

#99685

I need the precision of a decimal value to be dynamically controlled. Example:
DECLARE @input as decimal(16,6)
SET @input = 1.234567
DECLARE @precision INT
SET @precision = 2
-- SELECT CAST(@input AS DECIMAL(16, @precision)) <-- This does not work
-- SELECT CAST(@input AS DECIMAL(16, 2)) <-- This works This way, If @precision = 2
1.23 should be returned
If @precision = 3
1.234 should be returned
Is this possible?

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Kishore.P SSCrazy Eights Points: 8147 More actions · Answer 1

stelianx (5/6/2009)
I need the precision of a decimal value to be dynamically controlled. Example:
DECLARE @input as decimal(16,6)
SET @input = 1.234567
DECLARE @precision INT
SET @precision = 2
-- SELECT CAST(@input AS DECIMAL(16, @precision)) <-- This does not work
-- SELECT CAST(@input AS DECIMAL(16, 2)) <-- This works
This way,
If @precision = 2
1.23 should be returned
If @precision = 3
1.234 should be returned
Is this possible?

try the following way:

declare@inputdecimal (16, 6)

,@precisionint

,@outputvarchar (1000)

select@input= 1.234567

,@precision= 4

select@output= 'select cast (' + convert (varchar (100), @input) + ' as decimal (16, ' + convert (varchar (100), @precision) + ')) '

print(@output)

exec(@output)

stelianx SSChasing Mays Points: 604 More actions · Answer 2

That worked, thanks.

Now I am wondering how this can be used in a view 😀

I guess it is not possible. I was thinking about creating an udf to format decimal, but it's semed impossible to me.

arun.sas SSChampion Points: 11831 More actions · Answer 3

Hi Kishore,

When the value like 1.23999

Then it’s round the value

Select @input = 1.23999, @precision = 4

Result to 1.2400

For that better we use the direct round function?

Like select round(@input,@precision)

ARUN SAS

stelianx SSChasing Mays Points: 604 More actions · Answer 4

Well, it is "almost" good 😀

PRINT round(1.23456, 2)

displays 1.230000, while I need 1.23

I know it is not a good behaviour to format from SQL, but I really have to.

rgillings SSCrazy Points: 2114 More actions · Answer 5

If the value is for display purposes, and not required to be a decimal data type, you could format it as a string like below:

DECLARE @Input DECIMAL(16,6)

DECLARE @precision INT

SELECT @input = 1.23999, @precision = 4

SELECT CONVERT(VARCHAR, SUBSTRING(CONVERT(VARCHAR,round(@input,@precision)), 0, CHARINDEX('.', CONVERT(VARCHAR,@input),0) + @precision + 1))

stelianx SSChasing Mays Points: 604 More actions · Answer 6

stelianx

SSChasing Mays

Points: 604

May 8, 2009 at 8:58 am

#991175

Thanks!

This is what I needed.

deepikachaudhary31 SSC Journeyman Points: 85 More actions · Answer 7

deepikachaudhary31

SSC Journeyman

Points: 85

October 7, 2011 at 5:34 am

#1391641

Thanks this is exactly what i wanted:)

divyaeaga SSC Journeyman Points: 91 More actions · Answer 8

HI..

How to do the dynamic precision from a table

for ex: I have a table Currency with Rate (float) and Precision.

Currency Rate precision

AUD 12.12 5

AED 123.123123 7

I want to get these values as

AUD 12.12000

AED 123.1231230

Please help me. It is urgent!!

brijeshbajpai SSC Rookie Points: 37 More actions · Answer 9

rgillings - Friday, May 8, 2009 8:48 AM
declare @precision int =2
declare @ddd decimal(16,6)=10.9555
select convert(float,round(@ddd,@precision))