Re: Sybase convert oddity

Lee Fesperman wrote:
joeNOSPAM@xxxxxxx wrote:

rickpoleshuck@xxxxxxxxxxx wrote:

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.

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.


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.

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
.

Relevant Pages

  • Re: Adding two float values got strange result
    ... float a = 123.76f; ... Some computers have used binary arithmetic since the early 1950s, and nearly all computers have used binary arithmetic since the mid 1960s. ... The world is not carved up into exact decimal fractions. ... Distances between points are not always an exact number of millimeters. ...
    (comp.lang.java.programmer)
  • Re: read string to float
    ... real float with exact decimal point ... If you mean that you want to find a floating-point value which is ... Only one of those nine decimal fractions is exactly equal to a float, ... So back to your original problem: You have exact decimal-fraction ...
    (comp.lang.lisp)
  • Re: read string to float
    ... can anyone help me convert this string to exact float points ... number of binary digits (the length of which varies by the type of the ... of precision than the default precision". ...
    (comp.lang.lisp)
  • Re: read string to float
    ... can anyone help me convert this string to exact float points ... number of binary digits (the length of which varies by the type of the ... of precision than the default precision". ...
    (comp.lang.lisp)
  • Re: Sybase convert oddity
    ... The string input is ... exact, but when you pass 5.325 directly, ... the DBMS's SQL parser will convert that to ... Lee Fesperman, FFE Software, Inc. ...
    (comp.lang.java.databases)