How to format decimal places - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to format decimal places

Author

Topic

drew22299
Starting Member

26 Posts

Posted - 2009-02-24 : 03:41:18

Hi,

How can I round the value returned from the following code? I have tried adding ROUND(CONVERT(decimal,AVG(t1.Value1)),2) AS Value2 but it didn't work, is there a different way of doing this? The query also calls two functions so not sure if I have to use ROUND on any values returned by them or if ROUND should just be used for the following code?

CONVERT(decimal,AVG(t1.Value1)) AS Value2

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 03:46:12

wt is the datatype of value1?

convert(decimal(18,2),AVG(t1.Value1)) AS Value2
or use round(avg(t1.value1),2)

drew22299
Starting Member

26 Posts

Posted - 2009-02-24 : 03:55:38

I tried convert(decimal(18,2) and round(avg) but they didn't work. Since the query is calling two functions should the values returned be formatted within the funtion? The query calls two functions and two values are returned, the query then uses the two values in a calculation and the result of that calculation should be formatted to two decimal places.

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-02-24 : 04:17:22

USE AdventureWorksDW
GO
Create FUNCTION dbo.fn1
(
@productkey int
)
RETURNS decimal(18,3)
AS
BEGIN
DECLARE @price decimal(18,3)
SELECT @price=avg(UnitPrice) from dbo.FactInternetSales where ProductKey=@productkey
RETURN (@price)
END

select round(dbo.fn1(346)*ProductStandardCost,2) as 'price1',convert(decimal(18,2),dbo.fn1(346)*ProductStandardCost) as 'price' from dbo.FactInternetSales where ProductKey=346

OUTPUT
6501305.8400000 6501305.84
6501305.8400000 6501305.84
6501305.8400000 6501305.84
6501305.8400000 6501305.84
6501305.8400000 6501305.84
6501305.8400000 6501305.84

isk

drew22299
Starting Member

26 Posts

Posted - 2009-02-24 : 05:01:14

I have tried using ROUND and have tried the following but it still doesn't format value2 with 2 decimal places.

CONVERT(decimal, ROUND(AVG(t1.Value1),2)) AS Value2

CONVERT(integer,CONVERT(decimal, ROUND(AVG(t1.Value1),2))) AS Value2

Any ideas why this code isn't working?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-24 : 05:05:38

no need to convert into decimal after applying round function
if u want then convert(decimal(18,2),round(avg(t1.value1),2)) as value2

drew22299
Starting Member

26 Posts

Posted - 2009-02-24 : 05:10:21

I managed to fix it.

Thanks for your replies everyone

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources