13,045,657 members (66,276 online)
Rate this:
See more:
hello,

i have a problem in sql server 2005 with rounding functions. for example i have declared variable as decimal and set a value to it:

```DECLARE @v DECIMAL(8,3)
SET @v = 4.843```

now i have to round this value up to value 4.85. is there any possibility to do that? standard rounding functions (rounding, ceiling) can't do that.
Posted 26-Sep-12 1:25am
Amir Mahfoozi 26-Sep-12 12:25pm

Rounding has a definition and the output that you want is not rounding. Firstly define your algorithm and then implement it in a user defined scalar function. Good Luck

Rate this:

## Solution 1

Test it:
```DECLARE @v DECIMAL(8,3)
DECLARE @x DECIMAL(8,3)

SET @v = 4.839
SET @x = 0.001

CREATE TABLE #tt ([V] DECIMAL(8,3), [AkaModuloV] DECIMAL(8,3),  [RoundedV] DECIMAL(8,3))

WHILE (@v<4.85)
BEGIN
SET @v = @v + @x
INSERT INTO #tt([V], [AkaModuloV], [RoundedV])
SELECT @v AS [V], (@v - ROUND(@v, 2)) AS [AkaModuloV], [RoundedV]  = CASE
WHEN ((@v - ROUND(@v, 2))=0.000) THEN @v
WHEN ((@v - ROUND(@v, 2))<0.000) THEN ROUND(@v, 2)
WHEN ((@v - ROUND(@v, 2))>0.000) THEN ROUND(@v + (0.009 - (@v - ROUND(@v, 2))), 2)
END
END

SELECT *
FROM #tt

DROP TABLE #tt```

Results:
```V    AkaModuloV  RoundedV
4.840	0.000	4.840
4.841	0.001	4.850
4.842	0.002	4.850
4.843	0.003	4.850
4.844	0.004	4.850
4.845	-0.005	4.850
4.846	-0.004	4.850
4.847	-0.003	4.850
4.848	-0.002	4.850
4.849	-0.001	4.850
4.850	0.000	4.850```
Rate this:

## Solution 2

see below example
`select convert(numeric(18,2),2.237)`

you can use function convert
```convert(numeric(18,2),Input value)
or
convert(Decimal(18,2),Input value)```

Happy Coding!
:)
Rate this:

## Solution 3

try this

```DECLARE @v DECIMAL(8,3)
SET @v = 4.846
SELECT Cast(ROUND(@v, 2) as decimal(8,2))```

this will give me 4.85

```DECLARE @v DECIMAL(8,3)
SET @v = 4.843
SELECT Cast(ROUND(@v, 2) as decimal(8,2))```

this will give me 4.84
Rate this:

## Solution 4

i have created a function with code:

```GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create FUNCTION [dbo].[VAT_Rounding](@brutto DECIMAL(16,3))
RETURNS DECIMAL(15,2)
AS
BEGIN

DECLARE @vat DECIMAL(16, 3)
DECLARE @b DECIMAL(16, 3)
DECLARE @r DECIMAL(16, 3)
DECLARE @c DECIMAL(16, 3)

SET @vat = @brutto * 23 / 123
SET @b = ( SELECT ROUND(@vat, 2) )
SET @r = @vat - @b

IF @r > 0
BEGIN
SET @c = 0.01
SET @b = @b + @c
END

RETURN  @b

END
```

as i see it's similiar to Maciej Los. thanks for all Your support. :)

Top Experts
Last 24hrsThis month
 OriginalGriff 268 Graeme_Grant 125 ppolymorphe 124 Atlapure Ambrish 90 kp564 70
 OriginalGriff 4,753 RickZeeland 1,854 ppolymorphe 1,728 F-ES Sitecore 1,553 Dave Kreskowiak 1,349