Archives of the TeradataForum
Message Posted: Fri, 02 Jul 2004 @ 13:32:31 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 15 Jun 2023|