TSQL: The Discovery

The Finding

In SQL Server 2005, there is no inbuilt ROUNDUP and ROUNDDOWN function like on other SQL Server 2008 or 2012. To substitute this function is by adding/deducting .5 of the decimal point. For instance,  the number is 12345.96124  and you want to 2 decimal point round up or round down is by adding/deducting 0.005

DECLARE @num1 FLOAT;

DECLARE @num2 FLOAT;

SET @num1 = 123.12345;

SET @num2 = 12345.96124;

SELECT @num1 AS 'Number 1',

@num2 AS 'Number 2',

CAST(@num2 AS INTEGER) AS 'Integer',

FLOOR(@num2) AS 'Floor/round down',

CEILING(@num2) AS 'Celing/round up',

ROUND(@num2+0.005,2) AS 'Round Up with 2 decimal point',

ROUND(@num2-0.005,2) AS 'Round Down with 2 decimal point'

Hits: 1458

Comments are closed.