TSQL: The Discovery

[2022-06-29] As we consolidated all tech blogs into Dewapost website, we also post this blog into Dewapost.com and we reformat the content to suit within their guideline.

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



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.