Greetings. I have a 2 columns that represents the times of 2 events. It is a int16 datatype (I don't know why) representing hours and minutes on a 24 hour clock. I need to convert the 4 numbers into an interval to get the elapsed time between 2 times. For the minute part - I have found that I can cast it to a char, substring the last 2 characters and cast again to interval minutes.
For the Hour part - When I try to use rtrim to get down to the first 2 numbers, I get an JDBC adapter error or if I try to do some of the steps in Workspace advanced, instead of Framework, it just doesn't work. I get the same type of errors or results trying to pad with a 0 (zero) for the 1 digit hours or no full hour.
Cognos 10.2.2 Informix 12 DB.
How do I do rtrin and lpad, or how can I cast to get hh24mm?
Thanks
I think I figured it out:
Using a calculation in Framework - cast(substr(cast(lpad ([Database].[nb_queue].[approved_time] , 4,0),char(4)),1,2),interval hour)
and
cast(substr(cast([Database].[nb_queue].[approved_time] ,char(4)),-2,2),interval minute)