Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Aiii, INTERVALs!
 
From:   Maxwell, Donald

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016