Page 1 of 1
Embedded SQL Isolating Hour from Time Field
Posted: Tue Feb 21, 2023 5:01 am
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
Re: Embedded SQL Isolating Hour from Time Field
Posted: Tue Feb 21, 2023 5:07 am
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)
Re: Embedded SQL Isolating Hour from Time Field
Posted: Tue Feb 21, 2023 8:32 am
by bbunney
Yes sir that worked! Thank you so much for providing the solution. It's much appreciated.
Re: Embedded SQL Isolating Hour from Time Field
Posted: Tue Feb 21, 2023 1:19 pm
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.
Re: Embedded SQL Isolating Hour from Time Field
Posted: Tue Feb 21, 2023 5:13 pm
by bbunney
Thank you for letting me know about that. I haven't used timestamp_format before. I will try that as well.