Java Rounding function HALF_UP & HALF_DOWN SQL

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

General SQL Server Forums

Script Library

Java Rounding function HALF_UP & HALF_DOWN SQL

Author

Topic

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-20 : 18:02:03

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.

[CODE]

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

[/CODE]
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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources