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