Re: SQL question (converting dates)

From: Andrew Allen <andrew.allen_at_handleman.com>
Date: Tue, 25 Feb 2003 15:32:00 GMT
Message-ID: <3E5B7F66.4030101@handleman.com>

Jim Kennedy wrote:

> The difference is the days and fraction of a day.  So just multiply the
> result by the number of seconds in a day to get how many seconds the
> difference is.
> Jim
> "dbuckingham" <member8912_at_dbforums.com> wrote in message
> news:2570355.1046143974_at_dbforums.com...
> 

>>Hoping that someone can answer this easily.
>>
>>I have a procedure set up where sysdate is captured into a variable at
>>the beginning of the procedure and also at the end. I then wish to
>>subtract the two values to get the total duration of the job.
>>However, sysdate is of type dd/mm/yyyy 00:00:00 AM/PM and if you try to
>>do just the simple subtraction, then you exponential decimal number as a
>>result. Basically I wish to have the final answer in the format 00:00:00
>>disregarding the rest of the default formatting.
>>
>>Does anyone know any SQL commands / conversions that I could use
>>to do this?
>>
>>to_char, to_? type conversions etc.....
>>
>>Hope someone can help out.
>>

You could also consider using DBMS_UTILITY.GET_TIME. It returns the time in 1/100 second. You might code something like

start_time := dbms_utility.get_time;
<< do processing >>
duration := dbms_utility.get_time - start_time;

You would then have your execution time to the 1/100 second. Should be simple enough after that to format into hh:mm:ss.

--
AjA

Received on Tue Feb 25 2003 - 09:32:00 CST