Click here to Skip to main content
13,045,657 members (66,276 online)
Rate this:
 
Please Sign up or sign in to vote.
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
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
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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!
:)
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

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

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 11 Oct 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100