Re: Date substraction

Vaughan Mc Carthy wrote in message <6bc0hj$svq$1_at_hermes.is.co.za>...
>Hi there
>How do I sustract one date from another and get the answer in HH:MM:SS
>format ?? e.g. If I substract date from sysdate, I get a value. How do I
>covert THAT to HH:MM:SS ??

To do an accurate elapsed seconds calculation, you will need to convert everything to seconds and subtract at that level.

---
Example:
CREATE TABLE x AS SELECT sysdate X FROM dual

SELECT
  TO_CHAR(sysdate,'HH24:MI:SS') "Current",
  TO_CHAR(x,'HH24:MI:SS') "Started",
  (
    SUM( TO_CHAR(sysdate,'HH24')* 60 * 60 ) +
    SUM( TO_CHAR(sysdate,'MI')* 60 ) +
    TO_CHAR(sysdate,'SS')
  ) -
  (
    SUM( TO_CHAR(x,'HH24')* 60 * 60 ) +
    SUM( TO_CHAR(x,'MI')* 60 ) +
    TO_CHAR(x,'SS')
  ) "Elapsed Time (sec)"
FROM x
WHERE rownum = 1
GROUP BY x
---

Ideally you should also add the day (and even month) into calculation as you
could be subtracting 00:01:35 (Tue) from 24:58:10 (Mo). Also be careful as
the above SQL statement needs a GROUP BY clause because of the SUM - you
must only process a single row or else you will be adding multiple dates
into the single start date (which is the reason I added the rownum=1
criteria above).

regards,
Billy