Archives of the TeradataForum

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

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

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>>