Round down to nearest 0.5 - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Round down to nearest 0.5

Author

Topic

jsasala
Starting Member

2 Posts

Posted - 2013-03-20 : 11:38:51

I have searched around a lot and tried many different examples but can't seem to find the correct way. I basically need to round down to the nearest 0.5 decimal. So if the number is 3.455, I need it to show 3.0 and if the number is 3.8998 i need it to show 3.5. The number of decimal places that the original number results in can vary but i need it to only return 1 decimal place. Any suggestions? I appreciate any help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-03-20 : 13:10:51

do you mean this?


declare @test table
(
Field decimal(10,4)
)
insert @test
SELECT '3.456' AS Field UNION ALL
SELECT '3.7823' UNION ALL
SELECT '4.276' UNION ALL
SELECT '4.995'




SELECT Field,FLOOR(Field) + CASE WHEN  SIGN(0.5-(Field-FLOOR(Field))) >0 THEN 0 ELSE 0.5 END
FROM @test


output
----------------------------
Field   Rounded
----------------------------
3.4560	3.0
3.7823	3.5
4.2760	4.0
4.9950	4.5

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-20 : 14:59:04

Why not keep it simple?

SELECT  Field,
        FLOOR(2 * Field) / 2
FROM    @Test

N 56°04'39.26"
E 12°55'05.63"

jsasala
Starting Member

2 Posts

Posted - 2013-03-21 : 10:11:26

If the output is just a single cell, do i still need to use the table portion of the code, or is there a easier way of doing that?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-03-21 : 11:02:32

Just this part

,FLOOR(2 * Field) / 2 AS RoundedDown

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources