well, first of all you must have a typo, since the sql you show will return 14170, not 0.69 or anything like it. however, one thing springs out: Total * 100 + 100 is wrong IMHO, unless you are looking for CEILING functionallity. "round" would need : total * 100 +50 (which rounds to nearest integer at two decimal points, not to the nearest LARGER integer, which is what your sample does)
> -----Original Message----- > From: T&B [mailto:[EMAIL PROTECTED] > Sent: Thursday, December 13, 2007 4:27 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] Casting bug > > When I try: > > select cast( 141.70 * 100 as integer) > > I get 0.69, but should get 0.70 > > What's the problem? Seems like a bug. > > I tried some other numbers in place of 141.70, and they > worked OK, though I imagine there are others that have the > bug that I just haven't tried. > > The above is the isolated buggy part of a formula I use to > round of real amounts to two fixed decimal places: > > select > substr( > ' $' || cast( Total as integer ) || '.' || substr( cast( Total * 100 + 100 as > integer ), -2, 2 ) > , -10, 10 > ) > from ( select 141.70 as Total ) > ; > > which gives $141.69 but should give $141.70 > > Is there a more reliable way, using only SQLite? > > Thanks, > Tom > > > -------------------------------------------------------------- > --------------- > To unsubscribe, send email to [EMAIL PROTECTED] > -------------------------------------------------------------- > --------------- > > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
