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
Thanks for the reply...yes, it is giving the same error as u mentioned(interval field overflow ) while using HOUR(4) to second .Using Day(4) to second(0), it is working fine..
But we wanted output in form of hours to seconds only.
ss29
There's no datatype to hold an HOUR TO SECOND with more than 9999 hours.
So go with DAY TO SECOND or extract the various parts, calculate hours from days and then concatenate the parts to a string.
Dieter
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.