CODEWITHSUNDEEP
sqloracle-database
AloneInTheDark
AloneInTheDark

Reputation: 938

Subtract two dates in oracle sql

I'm trying to calculate how much time spent between a job's start and end time. Here is my query:

SELECT request_id, user_concurrent_program_name,
DECODE(phase_code,'C','Completed',phase_code) phase_code,
DECODE(status_code,'D', 'Cancelled' ,
'E', 'Error' , 'G', 'Warning',
'H','On Hold' , 'T', 'Terminating',
'M', 'No Manager' , 'X', 'Terminated',
'C', 'Normal', status_code) status_code,
actual_start_date, actual_completion_date, completion_text 
FROM apps.fnd_conc_req_summary_v
WHERE phase_code='C' AND
status_code='C'
ORDER BY 6 DESC

actual_start_date and actual_completion_date's type is DATE and the format is like below:

ACTUAL_START_DATE          ACTUAL_COMPLETION_DATE
3/25/2014 2:00:14 PM       3/25/2014 2:00:18 PM

The question is, how can i subtract these two columns and get something like 00:00:04?

In some cases, a job can be runned for two days and i should get the result like 2 days and 00:00:02

Upvotes: 0

Views: 1161

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

The following method extracts the day component and then formats the time using to_char():

select (trunc(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) || ' days and ' ||
        to_char(to_date('2000-01-01', 'YYYY-MM-DD') + (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE),
                'HH24:MI:SS'
               )
       )

In your main query, you would just add this to the end of the select list:

SELECT request_id, user_concurrent_program_name,
       DECODE(phase_code, 'C', 'Completed', phase_code) phase_code,
       DECODE(status_code, 'D', 'Cancelled' , 'E', 'Error' , 'G', 'Warning',
              'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X', 'Terminated',
              'C', 'Normal', status_code
              ) as status_code,
       actual_start_date, actual_completion_date, completion_text 
       (trunc(ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE) || ' days and ' ||
        to_char(to_date('2000-01-01', 'YYYY-MM-DD') + (ACTUAL_COMPLETION_DATE - ACTUAL_START_DATE),
                'HH24:MI:SS'
               )
       ) as Diff_DDHHMMSS
FROM apps.fnd_conc_req_summary_v
WHERE phase_code='C' AND
status_code='C'
ORDER BY 6 DESC;

Upvotes: 1

Related Questions