Archives of the TeradataForum

Message Posted: Tue, 23 Oct 2012 @ 20:42:13 GMT

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

Thanks Geoffrey and Fred, I think I have a solution now.

Geoffrey's suggestion worked, but I think Fred's hint is a bit easier (and retains the fractional seconds). However, I got an error using SECOND(2):

NumOfSecs*INTERVAL '00:00:01.00' HOUR TO SECOND(2) [3706] Syntax error: expected something between '(' and the integer '2'.

Removing the "(2)" part and casting the result as DECIMAL(10,4) seems to work. Apparently INTERVAL does not like float results.

Here is my final query:

     select cast( avg( case
                       when start_date <> end_date
                       then extract(second from end_time)
                          + extract(minute from end_time) * 60
                          + extract(hour   from end_time) * 3600
                          + 86400
                       else extract(second from end_time)
                          + extract(minute from end_time) * 60
                          + extract(hour   from end_time) * 3600
                       end) mod 86400 as decimal(10,4))
            * INTERVAL '00:00:01.00' HOUR TO SECOND as avg_time  from  bd_time_test;

I'm not too concerned about the impact or daylight savings time (but thanks for bringing that up).

Thanks again!

Bob

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