 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
Java Forum / Databases / July 2006Sybase convert oddity
select convert(numeric(16,2), 5.325),convert(numeric(16,2), '5.325') returns 5.32 5.33 I would like to understand the logic behind this behavior, if any. > select convert(numeric(16,2), 5.325),convert(numeric(16,2), '5.325') > [quoted text clipped - 3 lines] > > I would like to understand the logic behind this behavior, if any. Hi. This has nothing to do with Java, so you should post it to comp.databases.sybase. It probably has to do with the fact that a conversion to numeric will use an exact algorithm for it's input. The string input is exact, but when you pass 5.325 directly, the DBMS's SQL parser will convert that to an IEEE float which may not be able to exactly represent a decimal 5.325. The exact value may be slightly less, leading to the round-down. Joe Weinstein at BEA Systems > > select convert(numeric(16,2), 5.325),convert(numeric(16,2), '5.325') > > [quoted text clipped - 14 lines] > represent a decimal 5.325. The exact value > may be slightly less, leading to the round-down. You've undoubtedly nailed the problem, here. I just wanted to point out that this non-standard behavior by the DBMS. The SQL standard defines 5.325 as an exact numeric literal. An approximate numeric literal (floating point) must contain an 'E' suffix.  SignatureLee Fesperman, FFE Software, Inc. (http://www.firstsql.com) ============================================================== * The Ultimate DBMS is here! * FirstSQL/J Object/Relational DBMS (http://www.firstsql.com) > > > select convert(numeric(16,2), 5.325),convert(numeric(16,2), '5.325') > > > [quoted text clipped - 21 lines] > -- > Lee Fesperman, FFE Software, Inc. (http://www.firstsql.com) Actually the OP said that 5.329 also becomes 5.32, so it's something else... Joe > Actually the OP said that 5.329 also becomes 5.32, so > it's something else... Nope. I'm pretty sure you're listening to the voices in your head now. <g> Luke > > Actually the OP said that 5.329 also becomes 5.32, so > > it's something else... > > Nope. I'm pretty sure you're listening to the voices in your head now. <g> > > Luke I duplicated it. System.out.println("The Database version is " + c.getMetaData().getDatabaseProductVersion() ); Statement s = c.createStatement(); ResultSet r = s.executeQuery("select convert(numeric(16,2),5.329), convert(numeric(16,2), '5.329'), convert(varchar(30), 5.329 ), convert(numeric(16,2), convert(varchar(30), 5.329 )), convert(numeric(16,2), convert(double precision, 5.329)), convert(numeric(16,2), convert(float,5.329 ))"); while(r.next()) System.out.println( r.getString(1) + ", " + r.getString(2) + ", " + r.getString(3) + ", " + r.getString(4) + ", " + r.getString(5) + ", " + r.getString(6) ); It printed: The Database version is SQL Server/12.5/P/Generic/OS/1/OPT/Sat Jun 30 00:01:37 PDT 2001 5.32, 5.33, 5.329, 5.33, 5.33, 5.33 to remove the driver and JVM from suspicion I did: ResultSet r = s.executeQuery("select convert(varchar(30), convert(numeric(16,2),5.329))"); while(r.next()) System.out.println( r.getString(1) ); It printed: 5.32 >>> Actually the OP said that 5.329 also becomes 5.32, so >>> it's something else... [quoted text clipped - 39 lines] > > 5.32 Fair enough, but the OP didn't say it, just the same. ;^) Luke After I was reprimanded for posting to the wrong group, I replied to just Joe and reposted to the other group amending the post to show 5.329. There is a lot more traffic here. No answers, but more traffic :-) Thanks Rick > >>> Actually the OP said that 5.329 also becomes 5.32, so > >>> it's something else... [quoted text clipped - 43 lines] > > Luke > After I was reprimanded for posting to the wrong group, I replied to > just Joe and reposted to the other group amending the post to show > 5.329. > There is a lot more traffic here. No answers, but more traffic :-) Furry muff. Glad to be of help. ;^) Luke >>>select convert(numeric(16,2), 5.325),convert(numeric(16,2), '5.325') >>> [quoted text clipped - 18 lines] > non-standard behavior by the DBMS. The SQL standard defines 5.325 as an exact numeric > literal. An approximate numeric literal (floating point) must contain an 'E' suffix. 5.325 cannot be represented exactly as a floating-point number (try multiplying it by successive powers of 2 if you don't believe me), so it's not surprising that it rounds differently depending upon the precision. Consider this little test program (with apologies for the choice of language): #include <stdio.h> int main(int argc, char **argv) { double d = 5.325; float f = 5.325; printf("double value: %5.2lf\nfloat value: %5.2f\n", d, f); return 0; } which yields double value: 5.33 float value: 5.32 on IEEE 754-compliant hardware. This confirms Joe's hypothesis. I learnt this the hard way, writing FORTRAN code more than twenty years ago :-) David Harper Cambridge, England > >>>select convert(numeric(16,2), 5.325),convert(numeric(16,2), '5.325') > >>> [quoted text clipped - 48 lines] > David Harper > Cambridge, England Hi David! Thanks. I have the same sort of ancient history to have generated the hypothesis, but it's been disproven as the current issue by the latest JDBC code I posted. It's an odd internal DBMS imprecision, because I also tried 5.329, which the DBMS also converted to 5.32! Joe Free MagazinesGet these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...
|
Reply to this Message
|
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.