448,612 Members | 1,647 Online
Need help? Post your question and get tips & solutions from a community of 448,612 IT Pros & Developers. It's quick & easy.

# Math.Round and SQL Server Round

 P: n/a I'm trying to determine the best approach for rounding in an application I'm building. Unfortunately it appears as though SQL Server and VB.NET round in different ways. SQL Server select round(123.465,2) returns 123.470 Which I think is correct. VB.NET Math.Round(123.465, 2) returns 123.46 Through online help I have read that the VB.NET way of doing it is "called rounding to nearest, or banker's rounding." I guess my first question is what is correct (when determining Tax or Sale prices)? My next question is what is the best approach for standarizing? Can I make SQL Server act like VB.NET or VB.NET act like SQL Server? Are there additional functions either in SQL Server or VB.NET that I am missing? Also it appears as if the behavior is consistent throughout the VB.NET environment (formatcurrency and formatpercent). There are certain parts of the applicaiton where it makes sense for SQL Server to calculate some of the numbers and other where it makes sense for VB.NET to. Thanks Nov 21 '05 #1
10 Replies

 P: n/a tmeister wrote: I guess my first question is what is correct (when determining Tax or Sale prices)? Your accountant / CFO should be able to tell you which is the correct accounting method. Why is this calculation being left to the programmers? This is a financial matter, not a programming one. -- David Gugick Imceda Software www.imceda.com Nov 21 '05 #2

 P: n/a tmeister wrote: I guess my first question is what is correct (when determining Tax or Sale prices)? Your accountant / CFO should be able to tell you which is the correct accounting method. Why is this calculation being left to the programmers? This is a financial matter, not a programming one. -- David Gugick Imceda Software www.imceda.com Nov 21 '05 #3

 P: n/a I realize this is an accountant question, but if I'm playing every role, it's now my decision. I can't imagine that this type of question is not addressed with almost every ecommerce site on the Internet. Once a decision is made, I will need to know how to implement the appropriate functionality either on SQL Server or insided VB.NET. Thanks "David Gugick" wrote: tmeister wrote: I guess my first question is what is correct (when determining Tax or Sale prices)? Your accountant / CFO should be able to tell you which is the correct accounting method. Why is this calculation being left to the programmers? This is a financial matter, not a programming one. -- David Gugick Imceda Software www.imceda.com Nov 21 '05 #4

 P: n/a I realize this is an accountant question, but if I'm playing every role, it's now my decision. I can't imagine that this type of question is not addressed with almost every ecommerce site on the Internet. Once a decision is made, I will need to know how to implement the appropriate functionality either on SQL Server or insided VB.NET. Thanks "David Gugick" wrote: tmeister wrote: I guess my first question is what is correct (when determining Tax or Sale prices)? Your accountant / CFO should be able to tell you which is the correct accounting method. Why is this calculation being left to the programmers? This is a financial matter, not a programming one. -- David Gugick Imceda Software www.imceda.com Nov 21 '05 #5

 P: n/a "tmeister" wrote in message news:56**********************************@microsof t.com... I'm trying to determine the best approach for rounding in an application I'm building. Unfortunately it appears as though SQL Server and VB.NET round in different ways. SQL Server select round(123.465,2) returns 123.470 Which I think is correct. VB.NET Math.Round(123.465, 2) returns 123.46 VB Rounds a 5 to the nearest EVEN number so: 123.465 becomes 123.46 while 123.475 becomes 123.48 I personally consider this to be an incredibly inconsistent form of rounding and find that is causes numerous issues when programming. As far as I know only Microsoft rounds this way and it might only be VB. To Cause VB to round in a normal way take the int of +.5 so for the numbers above Int((Num*100)+.5))/100 Or if the Floor function takes an argument for the number of decimals Floor(Num+.005,2) I don't use VB much so I'm not sure about the Function Names. To Make SQL round the VB way is trickier, you need to determine whether the rounding digit(s) = 5 and then if the digit before the rounding digit is even or odd. Something like: DECLARE @Num as Numeric(8,4) Declare @Dig as Numeric(8,4) Declare @Dig2 as Integer Declare @Even as integer SET @Num = 123.465 SET @Dig = @Num * 1000 - (CAST(@Num*100 as integer)*10) SET @Dig2 = CAST(@Num * 100 as Integer) - (CAST(@Num * 10 as Integer)*10) SET @Even = Case When @Dig2 in (2,4,6,8,0) then 1 else 0 end SELECT Round(Case @Dig When 5 THEN CASE @Even When 1 THEN @Num-0.001 ELSE @Num+.001 END ELSE @Num END,2) Regards, Jim Nov 21 '05 #6

 P: n/a "tmeister" wrote in message news:56**********************************@microsof t.com... I'm trying to determine the best approach for rounding in an application I'm building. Unfortunately it appears as though SQL Server and VB.NET round in different ways. SQL Server select round(123.465,2) returns 123.470 Which I think is correct. VB.NET Math.Round(123.465, 2) returns 123.46 VB Rounds a 5 to the nearest EVEN number so: 123.465 becomes 123.46 while 123.475 becomes 123.48 I personally consider this to be an incredibly inconsistent form of rounding and find that is causes numerous issues when programming. As far as I know only Microsoft rounds this way and it might only be VB. To Cause VB to round in a normal way take the int of +.5 so for the numbers above Int((Num*100)+.5))/100 Or if the Floor function takes an argument for the number of decimals Floor(Num+.005,2) I don't use VB much so I'm not sure about the Function Names. To Make SQL round the VB way is trickier, you need to determine whether the rounding digit(s) = 5 and then if the digit before the rounding digit is even or odd. Something like: DECLARE @Num as Numeric(8,4) Declare @Dig as Numeric(8,4) Declare @Dig2 as Integer Declare @Even as integer SET @Num = 123.465 SET @Dig = @Num * 1000 - (CAST(@Num*100 as integer)*10) SET @Dig2 = CAST(@Num * 100 as Integer) - (CAST(@Num * 10 as Integer)*10) SET @Even = Case When @Dig2 in (2,4,6,8,0) then 1 else 0 end SELECT Round(Case @Dig When 5 THEN CASE @Even When 1 THEN @Num-0.001 ELSE @Num+.001 END ELSE @Num END,2) Regards, Jim Nov 21 '05 #7

 P: n/a tmeister wrote: I realize this is an accountant question, but if I'm playing every role, it's now my decision. I can't imagine that this type of question is not addressed with almost every ecommerce site on the Internet. Once a decision is made, I will need to know how to implement the appropriate functionality either on SQL Server or insided VB.NET. Thanks My point is if you are in charge, you should not take the word of anyone but an accountant or someone else who is in the know. The fact that SQL Server and VB round differently is not really an issue until you know how you must process the data using certified accounting principles. -- David Gugick Imceda Software www.imceda.com Nov 21 '05 #8

 P: n/a tmeister wrote: I realize this is an accountant question, but if I'm playing every role, it's now my decision. I can't imagine that this type of question is not addressed with almost every ecommerce site on the Internet. Once a decision is made, I will need to know how to implement the appropriate functionality either on SQL Server or insided VB.NET. Thanks My point is if you are in charge, you should not take the word of anyone but an accountant or someone else who is in the know. The fact that SQL Server and VB round differently is not really an issue until you know how you must process the data using certified accounting principles. -- David Gugick Imceda Software www.imceda.com Nov 21 '05 #9

 P: n/a James Goodwin wrote: "tmeister" wrote in messagenews:56**********************************@microso ft.com...I'm trying to determine the best approach for rounding in an applicationI'mbuilding. Unfortunately it appears as though SQL Server and VB.NET roundindifferent ways.SQL Serverselect round(123.465,2)returns123.470 Which I think is correct. VB.NETMath.Round(123.465, 2)returns123.46VB Rounds a 5 to the nearest EVEN number so:123.465 becomes 123.46 while 123.475 becomes 123.48 I personally considerthis to be an incredibly inconsistent form of rounding and find that iscauses numerous issues when programming. As far as I know only Microsoftrounds this way and it might only be VB. James, This is called "Banker's Rounding", and is widely used in finance (http://support.microsoft.com/kb/196652). It may be required by law in some places. There is no "correct" way to round the last digit away when a number ends in 5. Steve Kass Drew University To Cause VB to round in a normal way take the int of +.5 so for the numbersabove Int((Num*100)+.5))/100 Or if the Floor function takes an argument forthe number of decimals Floor(Num+.005,2) I don't use VB much so I'm notsure about the Function Names.To Make SQL round the VB way is trickier, you need to determine whether therounding digit(s) = 5 and then if the digit before the rounding digit iseven or odd. Something like:DECLARE @Num as Numeric(8,4)Declare @Dig as Numeric(8,4)Declare @Dig2 as IntegerDeclare @Even as integerSET @Num = 123.465SET @Dig = @Num * 1000 - (CAST(@Num*100 as integer)*10)SET @Dig2 = CAST(@Num * 100 as Integer) - (CAST(@Num * 10 as Integer)*10)SET @Even = Case When @Dig2 in (2,4,6,8,0) then 1 else 0 endSELECT Round(Case @Dig When 5 THEN CASE @Even When 1 THEN @Num-0.001ELSE @Num+.001 END ELSE @Num END,2) Regards,Jim Nov 21 '05 #10

 P: n/a James Goodwin wrote: "tmeister" wrote in messagenews:56**********************************@microso ft.com...I'm trying to determine the best approach for rounding in an applicationI'mbuilding. Unfortunately it appears as though SQL Server and VB.NET roundindifferent ways.SQL Serverselect round(123.465,2)returns123.470 Which I think is correct. VB.NETMath.Round(123.465, 2)returns123.46VB Rounds a 5 to the nearest EVEN number so:123.465 becomes 123.46 while 123.475 becomes 123.48 I personally considerthis to be an incredibly inconsistent form of rounding and find that iscauses numerous issues when programming. As far as I know only Microsoftrounds this way and it might only be VB. James, This is called "Banker's Rounding", and is widely used in finance (http://support.microsoft.com/kb/196652). It may be required by law in some places. There is no "correct" way to round the last digit away when a number ends in 5. Steve Kass Drew University To Cause VB to round in a normal way take the int of +.5 so for the numbersabove Int((Num*100)+.5))/100 Or if the Floor function takes an argument forthe number of decimals Floor(Num+.005,2) I don't use VB much so I'm notsure about the Function Names.To Make SQL round the VB way is trickier, you need to determine whether therounding digit(s) = 5 and then if the digit before the rounding digit iseven or odd. Something like:DECLARE @Num as Numeric(8,4)Declare @Dig as Numeric(8,4)Declare @Dig2 as IntegerDeclare @Even as integerSET @Num = 123.465SET @Dig = @Num * 1000 - (CAST(@Num*100 as integer)*10)SET @Dig2 = CAST(@Num * 100 as Integer) - (CAST(@Num * 10 as Integer)*10)SET @Even = Case When @Dig2 in (2,4,6,8,0) then 1 else 0 endSELECT Round(Case @Dig When 5 THEN CASE @Even When 1 THEN @Num-0.001ELSE @Num+.001 END ELSE @Num END,2) Regards,Jim Nov 21 '05 #11

### This discussion thread is closed

Replies have been disabled for this discussion.