PC Review


Reply
Thread Tools Rate Thread

Math.Round and SQL Server Round

 
 
=?Utf-8?B?dG1laXN0ZXI=?=
Guest
Posts: n/a
 
      29th Nov 2004
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
 
Reply With Quote
 
 
 
 
David Gugick
Guest
Posts: n/a
 
      29th Nov 2004
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

 
Reply With Quote
 
 
 
 
=?Utf-8?B?dG1laXN0ZXI=?=
Guest
Posts: n/a
 
      29th Nov 2004
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
>
>

 
Reply With Quote
 
James Goodwin
Guest
Posts: n/a
 
      29th Nov 2004
"tmeister" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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


 
Reply With Quote
 
David Gugick
Guest
Posts: n/a
 
      29th Nov 2004
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

 
Reply With Quote
 
Steve Kass
Guest
Posts: n/a
 
      29th Nov 2004


James Goodwin wrote:

>"tmeister" <(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>
>
>>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.
>
>

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 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
>
>
>
>

 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
nibbles of bits and bytes to make words go round and round Flasherly DIY PC 0 3rd Jul 2012 09:39 AM
Round Up and Round Down Time DaveMoore Microsoft Excel Worksheet Functions 1 1st Jan 2010 01:00 PM
round up and round down =?Utf-8?B?YXNwZ2s=?= Microsoft Access Queries 8 7th Apr 2007 01:37 AM
VS2005 - Going round and round in circles again :) postings@alexshirley.com Microsoft ASP .NET 0 22nd Jun 2006 12:26 PM
Round and round.... =?Utf-8?B?Q2hyaXMgTS4=?= Microsoft Access Database Table Design 2 18th Nov 2004 08:30 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 03:28 AM.