Archives of the TeradataForum

Message Posted: Fri, 02 Jul 2004 @ 13:32:31 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

I was just referring to the all too common overflow problem when computing the interval between two timestamps.

     SELECT  T2 - T1 DAY(4) TO SECOND

can only hold a maximum interval of 9999 days, or about 27 years. If you overflow, you have to start with the complicated expressions just to get a simple subtraction result.

As for the CAST function, I have come across the case where a source system will send a timestamp, say '2004-06-15 23:59:60.000000', with the 60 seconds indicating midnight. I have loaded this data by treating the date and time portions seperately as timestamp and interval, respectively, ie. CAST (('2004-06-15' (DATE)) AS TIMESTAMP ) + CAST ( '23:59:60.000000' AS INTERVAL HOUR TO SECOND ). This yields the correct (imho) timestamp of '2004-06-16 00:00:00', but would be easier if I could load as a YEAR TO SECOND interval and CAST directly to timestamp.

Consider the following SELECT,

     SELECT  TIMESTAMP '2004-06-15 23:59:60.000000' A
             ,TIMESTAMP '2004-06-16 00:00:00.000000' B
     WHERE   A = B;

This statement returns 0 rows. Is this correct? For me, this presents a problem loading data from multiple source systems that send differently formatted timestamps.

	<Prev	Next>		<<First	<Prev	Next>	Last>>