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/

Go to Top of Page

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"
Go to Top of Page

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?
Go to Top of Page

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"
Go to Top of Page
   

- Advertisement -