always rounding up in SQL Server

Question

0.00/5 (No votes)

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:

SQL

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

szataniel

Add a Solution

Comments

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

4 solutions

Solution 2

see below example

SQL

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!
:)

Posted 26-Sep-12 18:46pm

Aarti Meswania

Solution 3

try this

SQL

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

this will give me 4.85

SQL

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

this will give me 4.84

Posted 26-Sep-12 19:20pm

Santhosh Kumar Jayaraman

Solution 4

i have created a function with code:

SQL

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. :)

Posted 11-Oct-12 0:52am

szataniel

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2012-09-26T12:43:00

Test it:

SQL

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