Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Rounding Decimal value get wrong result Expand / Collapse
Author
Message
Posted Tuesday, January 25, 2011 8:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 6:24 PM
Points: 3, Visits: 10
hi all,

I try to give a expression to round some filed like below,
Round(Fields!unit_price.Value, Fields!Dom_Price_Places.Value)
---- Fields!unit_price.Value(decimal(18,8))
----Fields!Dom_Price_Places.Value(tinyint)
now I run into a one Fields!unit_price.Value=2.34500000, Fields!Dom_Price_Places.Value=2
What I get is
Round(CDec("2.34500000"), 2) return 2.34.

Round(2.34500000, 2) return 2.35

What's the difference here?
Is this a bug?

And help would be appreciated. Thanks,


Post #1053178
Posted Tuesday, January 25, 2011 6:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 6:24 PM
Points: 3, Visits: 10
Any Help? thanks
Post #1053612
Posted Tuesday, January 25, 2011 6:54 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
ada.xi (1/25/2011)
hi all,

I try to give a expression to round some filed like below,
Round(Fields!unit_price.Value, Fields!Dom_Price_Places.Value)
---- Fields!unit_price.Value(decimal(18,8))
----Fields!Dom_Price_Places.Value(tinyint)
now I run into a one Fields!unit_price.Value=2.34500000, Fields!Dom_Price_Places.Value=2
What I get is
Round(CDec("2.34500000"), 2) return 2.34.

Round(2.34500000, 2) return 2.35

What's the difference here?
Is this a bug?

And help would be appreciated. Thanks,




What is CDec? I can't find it in BOL anywhere.

My guess, CDec is converting "2.34500000" to 2.34, not 2.345.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1053615
Posted Tuesday, January 25, 2011 7:08 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, January 26, 2011 6:24 PM
Points: 3, Visits: 10
Cdec ---Convert to Decimal.

And (CDec("2.34500000")=2.34500000) return true.
Post #1053616
Posted Tuesday, January 25, 2011 7:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
ada.xi (1/25/2011)
Cdec ---Convert to Decimal.



In what? As I said, I can't find it anywhere in Books Online.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1053617
Posted Tuesday, January 25, 2011 7:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
Lynn Pettis (1/25/2011)
ada.xi (1/25/2011)
Cdec ---Convert to Decimal.



In what? As I said, I can't find it anywhere in Books Online.


Nevermind, I found it in the Visual Studio Help System, it is a VB function.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1053619
Posted Thursday, January 27, 2011 11:08 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 12:48 PM
Points: 2,818, Visits: 2,566
You say that
Round(CDec("2.34500000"), 2) return 2.34.

but it should be
Round(2.34500000, 2) return 2.35


What happens when the number is 2.35500000 does it return
2.35 or 2.36?

If it returns 2.36 you are encountering something called banker's rounding where a 5 will round up to an even number, but a 5 will round down if the round up number is odd. In other words the 5 will alway round in the direction of the even number whether that is up or down.

Without the long explanation of why banker's rounding exists, most people just code around it by testing the digit they are rounding and if it is a 5 they will add a value of 1 to that digit to make sure it rounds up.

Hope this helps.
Post #1054779
Posted Monday, January 31, 2011 8:32 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Friday, November 7, 2014 3:43 PM
Points: 509, Visits: 14,598
Daniel Bowlin (1/27/2011)

If it returns 2.36 you are encountering something called banker's rounding where a 5 will round up to an even number, but a 5 will round down if the round up number is odd. In other words the 5 will alway round in the direction of the even number whether that is up or down.

I think the use of CDec is invoking the bankers rounding somehow. Try dropping that function if you can; maybe put the rounding in the query if that's possible.

For what it's worth, here's something more from MSDN:
When the fractional part is exactly 0.5, CInt and CLng always round it to the nearest even number. For example, 0.5 rounds to 0 and 1.5 rounds to 2. CInt and CLng differ from the Fix and Int functions, which truncate, rather than round, the fractional part of a number. Also, Fix and Int always return a value of the same type as is passed in.

Though it doesn't include CDec in that list, I wouldn't be surprised if it behaves like CInt and CLng.
Post #1056228
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse