I took your example and it works for me.
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)
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)
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.
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.