how can we round the value in sql

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 can we round the value in sql

Author

Topic

boreddy
Posting Yak Master

172 Posts

Posted - 2009-08-14 : 06:44:13

i am getting the value like 23244.6776
i need to display 23244.677

in sql server i am not able to use trunc() function
is the any alternative here
thanks in advance

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-14 : 06:49:29

what datatype is it? Looks like a MONEY type.

you can use round in truncate mode (example)


DECLARE @foo MONEY SET @foo = 23244.6776

SELECT @foo

-- This rounds to 3 decimal
SELECT ROUND(@foo, 3) 

-- This truncates to 3 deciaml
SELECT ROUND(@foo, 3, 1)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

boreddy
Posting Yak Master

172 Posts

Posted - 2009-08-14 : 07:02:09

its data type is decimal
when applied above logc i am geeting 0 at last
like select round(234765.3433,3)
i am getting 234765.3430
i dont want to get 0 at last

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-14 : 07:12:21

you want 3 decimal places ?

then convert it to decimal(10,3)

convert(decimal(10,3), col)

KH
[spoiler]Time is always against us[/spoiler]

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-14 : 07:44:01

It's a fixed precision DECIMAL?

Khtan -- I think your CONVERT will actually round the value (example)


DECLARE @foo DECIMAL(20,4) SET @foo = 23244.6776
SELECT CONVERT(DECIMAL(20,3), @foo)

Result = 23244.678

I think you need to do this:


DECLARE @foo DECIMAL(20,4) SET @foo = 23244.6776
SELECT CONVERT(DECIMAL(20,3), ROUND(@foo, 3, 1))

Result = 23244.677

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources