SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Round down to nearest 0.5
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jsasala
Starting Member

2 Posts

Posted - 03/20/2013 :  11:38:51  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 03/20/2013 :  13:10:51  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 03/20/2013 :  14:59:04  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Why not keep it simple?
SELECT  Field,
        FLOOR(2 * Field) / 2
FROM    @Test


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

Edited by - SwePeso on 03/20/2013 14:59:29
Go to Top of Page

jsasala
Starting Member

2 Posts

Posted - 03/21/2013 :  10:11:26  Show Profile  Reply with Quote
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

Sweden
30265 Posts

Posted - 03/21/2013 :  11:02:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Just this part
,FLOOR(2 * Field) / 2 AS RoundedDown



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000