Embedded SQL Isolating Hour from Time Field

Discussions relating to writing software in ILE RPG (RPG IV). This includes both fixed and free format RPG.
Post Reply
bbunney
Posts: 45
Joined: Wed Jul 06, 2022 7:52 pm

Embedded SQL Isolating Hour from Time Field

Post by bbunney »

Hi There, I'm not sure how to isolate the hour portion of a 6 digit packed time field. I have a transaction file that has a CustNumber, TranDate (100 year date P5.0), TranTime(HMS P6.0) and TranAmount (P9.2). I need to count the number of transactions and total amount by customer by hour for a given date so I need to isolate the first two digits of the TranTime field to get the hour. IsoTranHour represents that 2 digit tran hour here.

select COUNT(*), CustNumber, TranDate, IsoTranHour, Sum(TranAmount)
from Tranfile
where TranDate = 44976
group by CustNumber, TranDate, IsoTranHour
order by CustNumber, TranDate, IsoTranHour
msddcb
Posts: 21
Joined: Wed Jul 28, 2021 5:12 am

Re: Embedded SQL Isolating Hour from Time Field

Post by msddcb »

I think this should work ...

Code: Select all

select COUNT(*), CustNumber, TranDate, substr(digits(IsoTranHour),1,2), Sum(TranAmount)
from Tranfile
where TranDate = 44976
group by CustNumber, TranDate, substr(digits(IsoTranHour),1,2)
order by CustNumber, TranDate, substr(digits(IsoTranHour),1,2)
bbunney
Posts: 45
Joined: Wed Jul 06, 2022 7:52 pm

Re: Embedded SQL Isolating Hour from Time Field

Post by bbunney »

Yes sir that worked! Thank you so much for providing the solution. It's much appreciated.
vhamberg
Posts: 12
Joined: Thu Jul 29, 2021 1:33 am

Re: Embedded SQL Isolating Hour from Time Field

Post by vhamberg »

You might also consider the following -

hour(timestamp_format(digits(TranTime), 'HH24MISS'))

This gives you a numeric result, you can get that with the first solution, too.

timestamp_format() is most useful if you need to do date calculations such as number of days between dates, perhaps. In the case given here, the date portion of the timestamp is the current date.
bbunney
Posts: 45
Joined: Wed Jul 06, 2022 7:52 pm

Re: Embedded SQL Isolating Hour from Time Field

Post by bbunney »

Thank you for letting me know about that. I haven't used timestamp_format before. I will try that as well.
Post Reply