CODEWITHSUNDEEP
sql-servernumbersdecimalscale
Ravi Bhartiya
Ravi Bhartiya

Reputation: 299

How To Control Scale Numbers in Decimal in Sql Server 2008

Is there any function in Sql Server 2008 that controls scale numbers.For example if we have a decimal number 123.456 and in function if pass 0 it should return 123, if i pass 1 then should return 123.4 if pass 2 then return 123.45 and if pass 3 then return 123.456.If there is no inbult function then Please let me know any user define function.Thanks, Ravi

Upvotes: 0

Views: 2569

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44316

CREATE FUNCTION f_test (
@a INT,
@b FLOAT
) RETURNS DECIMAL(12,6)
AS
BEGIN
  RETURN CAST(@b * POWER(10, @a) AS INT)*1.0 / POWER(10, @a)
END

SELECT dbo.f_test(2, 123.456) AS RESULT

RESULT
----------
123.450000

Upvotes: 1

Alex K.
Alex K.

Reputation: 175768

ROUND() can truncate;

select round(123.456, 3, 1) union
select round(123.456, 2, 1) union
select round(123.456, 1, 1) union
select round(123.456, 0, 1) 

>>123.456
>>123.450
>>123.400
>>123.000

If you don't want the trailing zeros remove them in the presentation layer, cast to a varchar or cast(round(123.456, 3, 1) as float)

Upvotes: 2

Related Questions