I have a field which has time duration in seconds...It has to be subtracted from a timestamp field. So, the first step would be to convert it to a HH:MM:SS format. I am trying to write a case statement and it works fine when the time is less than 1 hour. If the duration is over one hour, the casting is not done properly.
cast((case when duration/3600 = 0 then trim('00')||trim(':')||trim(duration/60)||trim(':')||trim(duration mod 60) when duration/3600 > 0 then trim(duration/3600)||trim(':')||trim((duration mod 3600)/60)||trim(':')||trim((duration mod 3600) mod 60) end) as time(0))
Is there any teradata function that directly converts the seconds into the HH:MM:SS format?