Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

MuMu88
Aged Yak Warrior

549 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  
 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.03 seconds. Powered By: Snitz Forums 2000