Can I compare these times in Query?

[epell]

I have a 4 position alphanumeric field that our software provider uses for a 24 hour time field. I would like to compare two time fields in a row against each other. Is there a way to specify the Result Field so it can compare these as actual time fields?

[jamief]

Re: Can I compare these times in Query?

if this were the RPG forum I would say Yes...does this need to be done in query?

[epell]

Re: Can I compare these times in Query?

Only because I don't know RPG :-)

[vinomails]

Re: Can I compare these times in Query?

Hi Eric,

I assume that your record looks like below

time-in     time-out    difference
1240        1530         02:50:00

select (time(substr('1530',1,2)||':'||substr('1530',3,2)) -        
  int(substr('1240',1,2)) hours ) - int(substr('1240',3,2)) minutes
from                                                               
sysibm/sysdummy1

Hope this helps!

If this is not what you expect, then please send us a sample record and the expected output.

Regards,
Vinothkumar S.

[epell]

Re: Can I compare these times in Query?

That wasn't quite what I needed but you did put me on the right path! Thank you very much. Now that I properly formatted the time field I was able to put the date and time into a timestamp function and can now calculate date/time differences.

Here's how I did it, these are all in the Define result fields in Query:

[B]Field     Expression[/B]
DATE      date(digits(datefield))
TIME      time(substr(timefield,1,2)||':'||substr(timefield,3,2))
DATTIM    timestamp(date,time)