I took your example and it works for me.
Select
cast(cast(timestamp '2005-03-18 15:50:04.000000' as varchar(19)) as timestamp(0)) as A1,
cast(cast(timestamp '2005-03-18 14:30:30.000000' as varchar(19)) as timestamp(0)) as A2,
A1 - A2 hour(4) to second(0)
returns
2005-03-18 15:50:04 2005-03-18 14:40:30 1:19:34
now, you probably need to check that you don't have some values in your table that are exceeding the time allowed for an hour(4) because honestly that is the only thing that should cause the issue that you are seeing. Is the error your getting interval field overflow when you indicate above that it isn't executing? If so then you probably have a start/end time that exceeds 9999 hours (whether that is correct data or not is a different matter).
Another option you have is to use the Day(4) to second(0) and then parse that to convert the Day into hours and add that to the hours.
So if your result was 2 1:19:34 this would be 2 days 1hr 19 min and 34 seconds of execution time. So you could just multiply the 2 x 24hrs in a day to get 48 and then add that to the 1:19:34 to get 49:19:34 (of course the sql to do that is a bit messier).
but ultimately i think the root issue assuming that your getting an interval field overflow is that you have data the exceeds the limits of hour(4)
Hi Guys,
I have ID's with multiple TASK_END_TIME and TASK_CRETD_TIME for different task in the below format
3/18/2005 15:50:04.000000 and 3/18/2005 14:30:30.000000 respectively.
Now firstly I would require total cycle time in the format of "Hour:Minute:Seconds" for each task and
I tried to write a query with Hour(4) to Seconds conversion but not getting executed.
REPLACE VIEW DB_VIEW.TASK_VIEW AS
LOCKING ROW FOR ACCESS
SELECT
CAST(CAST (TASK_END_TIME AS VARCHAR(19)) AS TIMESTAMP(0)) AS A1,
CAST(CAST (TASK_CRETD_TIME AS VARCHAR(19)) AS TIMESTAMP(0)) AS A2,
(A1 - A2 Hour(4) TO SECOND(0)) AS Cycle_Time,
A.*
FROM DB.TASK_TABLE A;
But in the same query If I change Hour(4) with day(4), it is working fine and giving me output like "0 01:19:34"
Later I have to get Average cycle time for each ID in one of the select query using above view as a Source table.
Could you please help me on getting desired output.
Thanks in advance.