Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 05 Sep 2005 @ 13:55:43 GMT


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


Subj:   Re: INTERVAL math and error 7453
 
From:   Dieter Noeth

Mike Felts wrote:

          > select ActArriveDTM, SchedArriveDTM
          > ,cast((extract(day From (ActArriveDTM - SchedArriveDTM day(4) To
          > minute)) * 1440) +
          >  (extract(hour from (ActArriveDTM - SchedArriveDTM day(4) To minute)) *
          > 60)
          > +
          >  (extract(minute From (ActArriveDTM - SchedArriveDTM day(4) To minute)))
          > as
          > INTEGER)
          > from Table
  ActArriveDTM and SchedArriveDTM are defined as timestamp(6). This returns the expected result (i.e. the number of minutes between the two timestamps) except where the number of days between the two timestamps is greater than 9,999. Unfortunately, some of my data does indeed do this.  


This is a real "late arrival" ;-)

  In these cases an error is returned, 7453: Interval field overflow.  


  Is there a way to extend it so that more than 9,999 days can be considered?  


Would be so easy if Teradata allowed more than 4 digits (like Standard SQL)

I think this will return the requested information:

     (cast(ActArriveDTM as date) - cast(SchedArriveDTM as date)) * 1440
    + abs(cast((cast(ActArriveDTM as time)
              - cast(SchedArriveDTM as time) minute(4)) as int))

Dieter



     
  <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: 15 Jun 2023