Writing a Date Expression
The following example returns the processing time for a process/job in the process request table. This query returns the process instance, process name, operator ID, run status, run control ID and processing time (which is process end date/time - process begin date/time expressed as seconds).
This is how the expression looks (type character).
The key to this is the following:
to_char(round((ENDDTTM - BEGINDTTM) * 24 * 60 * 60))
This gives the difference in seconds. However if you just want the answer in days it would be just:
to_char(round(ENDDTTM - BEGINDTTM))
Here's the Query SQL:
SELECT A.PRCSINSTANCE
, A.PRCSNAME
, TO_CHAR(A.RUNDTTM
,'YYYY-MM-DD-HH24.MI.SS."000000"')
, A.OPRID
, A.RUNSTATUS
, A.RUNCNTLID
, (CASE WHEN ENDDTTM IS NULL THEN 'Not Available'
ELSE to_char(round((ENDDTTM - BEGINDTTM) * 24 * 60 * 60)) END)
AS PROCESSING_TIME
FROM PSPRCSRQST A
No Comments