Myrna van Lunteren wrote: > > Well, I can see that smaller differences are tested (test > lang/timestampArith.java) but these values were pulled out of Sun's > (jdbc) compliance test suite, and I thought one shouldn't modify those > values? Lance, you may know more about that detail... > > Should timestampdiff be able to handle the range of the two timestamps > in this particular query?
So one year is roughly 3.1e7 seconds, which is 3.1e16 fractions of a second. ODBC defines SQL_TSI_FRAC_SECOND as " fractional seconds are expressed in billionths of a second". I assume this is US billion, 1e-9. So 2 years (roughly the example you have) should be 6.2e16, outside the range for an INTEGER but within the range for a BIGINT. Is this function always returning an INTEGER? My guess is that it's up to the engine to define the range it handles, not ODBC or JDBC. Dan. > Myrna > > On 11/28/05, *Lance J. Andersen* <[EMAIL PROTECTED] > <mailto:[EMAIL PROTECTED]>> wrote: > > Myrna, > > Try having the timestamp values closer together and give it a go. > > -lance > > > Myrna van Lunteren wrote: > >> Hi, >> >> Is this a bug? It seems to me it is... >> >> In ij: >> ij> create table tab1(TMSTCOL1 TIMESTAMP, TMSTCOL2 TIMESTAMP); >> 0 rows inserted/updated/deleted >> ij> INSERT INTO tab1 VALUES ({ts '1994-06-12 11:20:10'},{ts >> '1996-05-10 10:07:05'}); >> 1 row inserted/updated/deleted >> ij> SELECT {FN >> TIMESTAMPDIFF(SQL_TSI_FRAC_SECOND,TMSTCOL1,TMSTCOL2)} FROM tab1; >> 1 >> ----------- >> ERROR 22003: The resulting value is outside the range for the data >> type INTEGER. >> >> Thx, >> Myrna > >
