[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
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'
Credits
Featured photo generated by OpenAI Dalle3.
