TSQL Round Up function for Decimal Value

These few weeks I was working on a project which needs to deal with decimal place.
I was struggle with it for very long time =(.
One of the processes on the program is to round up the value from 4 decimal (4dp) places to 3 decimal places (3dp)

I had done some research; I do not think there is a Default function from MSSQL that I can use to force it to round up a value. Although it does has a function for you to round down a value.

Anyway I have created my User define function which allow me to specific the number of decimal place that I want to round up to and the value that I would like to round.

CREATE FUNCTION [dbo].[RoundUpToGivenDecimalPlace]
(
— Add the parameters for the function here
@SourceValue
MONEY= 0,
@DecimalPlace INT = 1
)
RETURNS FLOAT
AS
BEGIN

DECLARE @RoundResult MONEY

DECLARE @ValueToAdd MONEY
SET @ValueToAdd = @DecimalPlace +1

SET @ValueToAdd = 4.0/POWER(10,@ValueToAdd)

SET @RoundResult= ROUND(@SourceValue+@ValueToAdd, @DecimalPlace)

— Return the result of the function
RETURN @RoundResult
End

Hope your find it useful.

Leave a Reply