Re: Help needed with Date Functions

From: Graham C Thornton <graham.thornton_at_ln.ssw.abbott.com>
Date: Mon, 19 Apr 1999 11:21:20 -0500
Message-ID: <7ffl73$am6@news.abbott.com>

Paul Wiles wrote in message <371b2db3_at_newsread3.dircon.co.uk>...
>That's not what I'm after. I'm looking for a simple way to calculate the
>difference between two dates in seconds. The two dates may be months apart
>and I don't want to do any fancy arithmetic like working out the difference
>in days, multiplying by 24*3600 then adding and subtracting the seconds
>since midnight ('SSSS') for each of the boundary dates.

And I want to be sitting on a beach in the Cayman Islands drinking Magharitas
and watching the sun go down, but it ain't gonna happen anytime soon...

>
>Surely there must be a simpler way??
>

Perhaps this will work for you..

SQL> select ((

  1  to_number( to_char( sysdate,'J')) -
  2  to_number( to_char( sysdate-5, 'J' ))) * 86399) + (
  3  to_number( to_char( sysdate,'SSSSS')) -
  4  to_number( to_char( sysdate-5, 'SSSSS' )))

  5 diff1
  6* from dual
SQL> /      DIFF1

---

    431995

SQL> Substitute the sysdate and sysdate-5 for your own dates.

Ok, so maybe it isn't as neat as the Sybase example, but it works and is relatively simple. If you
find a better method please let me know.

>
>D. Buecherl wrote in message <371B223E.4CD736D7_at_nkk.net>...
>>date1 - date2 gives you the number of days between two dates, therefore
>>24 * 3600 * (sysdate - trunc(sysdate, 'MONTH'))
>>gives you the number of seconds elapsed within the current month
>>
>>Dieter
>>
>>Paul Wiles schrieb:
>>
>>> Hi
>>>
>>> Can someone tell me how to find the difference in seconds between two
>>> dates - is there an equivalent to the Transact SQL function:
>>>
>>> datediff(ss,date1,date2).
>>>
>>
>
>
Received on Mon Apr 19 1999 - 11:21:20 CDT