Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Wed, 30 Jun 2004 @ 23:58:41 GMT


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


Subj:   Aiii, INTERVALs!
 
From:   Frank C. Martinez IV

Hello all!

I have two TIMESTAMP(6) values in a table that captures the times for a canary query. In order to get the total run time for any particular canary run (record), I do the following:

     SELECT (run_end - run_start) hour(2) TO second(6)
       FROM dp.t_canary
       SAMPLE 1;

and I get 0:00:06.560000, which is a good start. Unfortunately, I only want one significant digit, not six. So I tried:

     SELECT (run_end - run_start) hour(2) TO second(1)
       FROM dp.t_canary
       SAMPLE 1;

which blew up with 7453: Interval field overflow.

I tried:

     SELECT ((run_end - run_start) hour(2) TO second(1)) (FORMAT 'hh:mm:ss.s(1)')
       FROM dp.t_canary
       SAMPLE 1;

which blew up with 5407: Invalid operation on an ANSI Datetime or Interval value.

The only thing I got to work is:

     SELECT SUBSTR(CAST(((run_end - run_start) hour(2) TO second(6)) AS CHAR(16)), 1, 11)
       FROM dp.t_canary
       SAMPLE 1;

which, of course, only truncates the field, not round.

So how stupid am I, and how can I replace my brain?

iv



     
  <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