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