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 10 12944
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
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
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
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
"tmeister" <tm******@discu ssions.microsof t.com> wrote in message
news:56******** *************** ***********@mic rosoft.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
"tmeister" <tm******@discu ssions.microsof t.com> wrote in message
news:56******** *************** ***********@mic rosoft.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
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
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
James Goodwin wrote: "tmeister" <tm******@discu ssions.microsof t.com> wrote in message news:56******* *************** ************@mi crosoft.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(12 3.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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Nicolas |
last post by:
Why does for the same code I got different result
the VB code gave me what I want why not the csharp?
Thank you for your help...
CSHARP CODE
int sx, sy;
double sdegrees;
sdegrees = (90 - (5/15) * 90) * (System.Math.PI / 180);
|
by: ng_mr |
last post by:
No, not a question about "banker's rounding" or whatever it's called.
I want to round a double to the nearest 100th, so I perform the
following:
// original is a double
double result = Math.Round( original, 2 );
But I'm not happy with the results. E.g. if original is
|
by: Test User |
last post by:
Hi all,
I have learnt that if I want to round 0.5 to an integer the result should be
1, This is also the case if I do it in SQL server 2000, but if I do it in
VB.NET the result will be 0.
Other rounding results: 1.5 = 2 in SQL and 2 in VB.NET which I believe is
corrrect
2.5 = 3 in SQL and 2 in VB.NET which I believe is wrong.
|
by: tmeister |
last post by:
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
|
by: Mitchell Vincent |
last post by:
Just making sure I'm not missing the boat here, but are there any
special routines for doing currency math (fixed precision stuff) in .NET?
The wonderful problems of doing math on decimals tend to shine when
writing accounting software :-)
How are others dealing with this?
--
- Mitchell Vincent
| |
by: Chris Davoli |
last post by:
The folllowing will round to 526, but it should round to 527. It works
correctly for all other numbers, except for this one. Does anybody know of a
bug in Math.Round?
Dim ldecWater As Decimal = 526.5
CType(Math.Round(ldecWater, 0), String)
--
Chris Davoli
|
by: David Coleman |
last post by:
I am running VS 2003 and have applied SP1. (On WinXP SP2, .Net 1.1)
In the Command Window I get the following
? Math.Round(0.715, 2)
0.72
? Math.Round(0.725, 2)
0.72
? Math.Round(0.735, 2)
0.74
|
by: Zeng |
last post by:
Math.Round has good behavior as following:
Math.Round(3.45, 1); //Returns 3.4. The last '5' is thrown away because 4 is
even
Math.Round(3.75, 1); //Returns 3.8. The last '5' is used because '7' is odd
However, if format.NumberDecimalDigits is 1
decimal d = 3.45M;
d.ToString( "F", format ); //Return 3.5 - this is different from Math.Round;
|
by: =?Utf-8?B?UmVuZQ==?= |
last post by:
Hello everyone
I have a problem with Math.Round, it´s ocurring some strange:
Math.Round(12.985) = 12.98, it´s wrong. It should be: 12.99
Why?? What is the problem?
Help ME !!!!
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |