Re: Compute difference between date/time fields problem.

In article <1993Mar19.093058.1_at_mcvax2.d48.lilly.com> rzx2134_at_mcvax2.d48.lilly.com writes:
>>>I have a table that contains a date/time field. Sometime later
>>>I would like Oracle to compute the difference between the current
>>>date/time and the data field. This result should be expressed in
>>>minutes.
>>>
>>>I would like to implement the solution through Pro-C. Could this
>>>be done through SQLPLUS?
>>>
>>>Replies, e-mail or through the newsgroup, are appreciated.
>>>
>>>John
>
>I have a table with start and complete dates. There was a need to produce
>a SQL Report with the time that it took for a report to be executed. I
>tried subtracting the dates but with you place a timestamp on the date field,
>it seems that Oracle will give you a julian number instead of a formatted date.
>To solve this problem I came up with the following SQL*Plus statement. This
>is crude but it works:
>
>select code_desc, rpt_name, rqstr_user_id, replace (
>/* Number of days */
> to_char(
> to_date(start_dtm,'DD-MON-YYYY') -
> to_date(cmplt_dtm,'DD-MON-YYYY'),'00')
> || ':' ||
>/* Number of hours */
> to_char ( 12 -
> to_number (
> to_char(
> to_date(to_char(cmplt_dtm,'SSSSS') -
> to_char(start_dtm,'SSSSS'),'SSSSS'),
> 'HH')
> )
> ,'00')
> || ':'||
>/* Number of minutes and seconds */
> to_char(
> to_date(to_char(cmplt_dtm,'SSSSS') -
> to_char(start_dtm,'SSSSS'),'SSSSS'),
> 'MI:SS')
> ,' ')
> Execution_Time
>from rad_rpt_list rli, rad_rpt_usage rus, rad_validation rva
>where rli.rpt_id = rus.rpt_id
>and rli.aptn_code = nvl(upper('&aptn_code'),rli.aptn_code)
>and rva.valid_code = rli.aptn_code
>and rva.column_name = 'APTN_CODE'
>order by 1,2

Here's a slightly shorter solution, which takes advantage of the fact that date-date returns the number of days between the 2 dates.

SELECT code_desc, rpt_name, rqstr_user_id, trunc(cmplt_dtm-start_dtm) || ':' ||
to_char(sysdate + (cmplt_dtm-start_dtm-trunc(cmplt_dtm-start_dtm)), 'HH24:MI:SS') Execution_Time
FROM rad_rpt_list rli, rad_rpt_usage rus, rad_validation rva WHERE rli.rpt_id = rus.rpt_id

AND rli.aptn_code = nvl(upper('&aptn_code'),rli.aptn_code)
AND rva.valid_code = rli.aptn_code
AND rva.column_name = 'APTN_CODE'