Thanks all, for your input, I'll go tweak those values.
Myrna
On 11/28/05, Satheesh Bandaram <[EMAIL PROTECTED]> wrote:
Yes, Timestampdiff is coded to return an INT, which has the following range. May be it could return a BIGINT if argument is of type SQL_TSI_FRAC_SECOND.
So, the query is exceeding the range.
Satheesh
Daniel John Debrunner wrote: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
