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
 General SQL Server Forums
 Script Library
 Java Rounding function HALF_UP & HALF_DOWN SQL
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MuMu88
Aged Yak Warrior

547 Posts

Posted - 05/20/2013 :  18:02:03  Show Profile  Reply with Quote
Here is a SQL function that performs Java HALF_UP and HALF_DOWN rounding; the usage of the function is self-explanatory.
Pass a numeric value and 'UP' as second parameter if you want to perform HALF_UP or 'DOWN' as second parameter if you want to perform HALF_DOWN.



IF OBJECT_ID('dbo.Java_HALF_UP_DOWN') IS NOT NULL DROP FUNCTION dbo.Java_HALF_UP_DOWN;

GO
CREATE FUNCTION dbo.Java_HALF_UP_DOWN
(
	@nval AS NUMERIC(12,5),
	@type AS VARCHAR(8)
)
RETURNS NUMERIC(12,5)
AS 
BEGIN
	DECLARE	@cval AS NUMERIC(12, 5)
	DECLARE @sval AS NUMERIC(2,1)
	DECLARE @tval AS INT
	
	SET @sval = CASE WHEN @nval < 0 THEN -1 ELSE 1 END
	SET @cval = ABS(@nval) - FLOOR(ABS(@nval))
	
	RETURN (CASE WHEN (@type = 'DOWN') and @cval <= 0.5 THEN FLOOR(ABS(@nval))
		WHEN (@type = 'UP') and @cval < 0.5 THEN FLOOR(ABS(@nval))
		ELSE CEILING(ABS(@nval)) END) * @sval;
END
GO



USAGE: 

SELECT 88887.50001 as val, dbo.Java_HALF_UP_DOWN(88887.50001, 'UP') as Java_Half_UP
UNION ALL
SELECT 88887.49999 as val, dbo.Java_HALF_UP_DOWN(88887.49999, 'UP')
UNION ALL
SELECT 88887.50000 as val, dbo.Java_HALF_UP_DOWN(88887.50000, 'UP')
UNION ALL
SELECT -88887.50000 as val, dbo.Java_HALF_UP_DOWN(-88887.50000, 'UP')
UNION ALL
SELECT 5.5 as val, dbo.Java_HALF_UP_DOWN(5.5, 'UP')
UNION ALL
SELECT 2.5 as val, dbo.Java_HALF_UP_DOWN(2.5, 'UP')
UNION ALL
SELECT 1.6 as val, dbo.Java_HALF_UP_DOWN(1.6, 'UP')
UNION ALL
SELECT 1.1 as val, dbo.Java_HALF_UP_DOWN(1.1, 'UP')
UNION ALL
SELECT 1.0 as val, dbo.Java_HALF_UP_DOWN(1.0, 'UP')
UNION ALL
SELECT -1.0 as val, dbo.Java_HALF_UP_DOWN(-1.0, 'UP')
UNION ALL
SELECT -1.1 as val, dbo.Java_HALF_UP_DOWN(-1.1, 'UP')
UNION ALL
SELECT -1.6 as val, dbo.Java_HALF_UP_DOWN(-1.6, 'UP')
UNION ALL
SELECT -2.5 as val, dbo.Java_HALF_UP_DOWN(-2.5, 'UP')
UNION ALL
SELECT  -5.5 as val, dbo.Java_HALF_UP_DOWN(-5.5, 'UP');


RESULTS:
val             JAVA_Half_UP
88887.50001	88888.00000
88887.49999	88887.00000
88887.50000	88888.00000
-88887.50000	-88888.00000
5.50000  	6.00000
2.50000  	3.00000
1.60000  	2.00000
1.10000  	1.00000
1.00000  	1.00000
-1.00000 	-1.00000
-1.10000 	-1.00000
-1.60000 	-2.00000
-2.50000 	-3.00000
-5.50000 	-6.00000

SELECT 88887.50001 as val, dbo.Java_HALF_UP_DOWN(88887.50001, 'DOWN') as Java_Half_DOWN
UNION ALL
SELECT 88887.49999 as val, dbo.Java_HALF_UP_DOWN(88887.49999, 'DOWN')
UNION ALL
SELECT 88887.50000 as val, dbo.Java_HALF_UP_DOWN(88887.50000, 'DOWN')
UNION ALL
SELECT -88887.50000 as val, dbo.Java_HALF_UP_DOWN(-88887.50000, 'DOWN')
UNION ALL
SELECT -88887.5881 as val, dbo.Java_HALF_UP_DOWN(-88887.5881, 'DOWN')
UNION ALL
SELECT 5.5 as val, dbo.Java_HALF_UP_DOWN(5.5, 'DOWN')
UNION ALL
SELECT 2.5 as val, dbo.Java_HALF_UP_DOWN(2.5, 'DOWN')
UNION ALL
SELECT 1.6 as val, dbo.Java_HALF_UP_DOWN(1.6, 'DOWN')
UNION ALL
SELECT 1.1 as val, dbo.Java_HALF_UP_DOWN(1.1, 'DOWN')
UNION ALL
SELECT 1.0 as val, dbo.Java_HALF_UP_DOWN(1.0, 'DOWN')
UNION ALL
SELECT -1.0 as val, dbo.Java_HALF_UP_DOWN(-1.0, 'DOWN')
UNION ALL
SELECT -1.1 as val, dbo.Java_HALF_UP_DOWN(-1.1, 'DOWN')
UNION ALL
SELECT -1.6 as val, dbo.Java_HALF_UP_DOWN(-1.6, 'DOWN')
UNION ALL
SELECT -2.5 as val, dbo.Java_HALF_UP_DOWN(-2.5, 'DOWN')
UNION ALL
SELECT  -5.5 as val, dbo.Java_HALF_UP_DOWN(-5.5, 'DOWN');


RESULTS:
val             JAVA_Half_DOWN
88887.50001	88888.00000
88887.49999	88887.00000
88887.50000	88887.00000
-88887.50000	-88887.00000
-88887.58810	-88888.00000
5.50000  	5.00000
2.50000  	2.00000
1.60000  	2.00000
1.10000  	1.00000
1.00000  	1.00000
-1.00000 	-1.00000
-1.10000 	-1.00000
-1.60000 	-2.00000
-2.50000 	-2.00000
-5.50000 	-5.00000


Refer to the following site for more information
http://docs.oracle.com/javase/6/docs/api/java/math/RoundingMode.html

EDITED: Much simpler solution without string manipulations

Edited by - MuMu88 on 05/21/2013 11:25:12
  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.11 seconds. Powered By: Snitz Forums 2000