select LPEM as "Employee", LPLC as "LaborCode",
to_date(char(lpsdt), 'YYYYMMDDHH24MISS') as "Start Date/Time",
to_date(char(lpfdt), 'YYYYMMDDHH24MISS') as "Finish Date/Time",
to_date(char(lpfdt), 'YYYYMMDDHH24MISS') - to_date(char(lpsdt), 'YYYYMMDDHH24MISS') as "Time Spent",
from labor.empmngmnt
select LPEM as "Employee", LPLC as "LaborCode",
to_date(char(lpsdt), 'YYYYMMDDHH24MISS') as "Start Date/Time",
to_date(char(lpfdt), 'YYYYMMDDHH24MISS') as "Finish Date/Time",
timestampdiff (2, cast (to_date(char(lpfdt), 'YYYYMMDDHH24MISS') - to_date(char(lpsdt), 'YYYYMMDDHH24MISS') as char(22))) as "Time Spent",
from labor.empmngmnt
This has the potential to solve just like your previous question. The packed decimal value is 14 digits. Convert them to timestamps, and compute the difference with timestampdiff. It does require a reasonable estimate of what you expect, seconds, days, etc.
Open in new window
That query will return the number of seconds between the two times. Adding the computed number of seconds to time ('00.00.00') will give you an answer as HH:MM:SS. If you're expecting an answer of days or more, what format do you need?
Kent