Re: Help needed with Date Functions

In article <7ffl73$am6_at_news.abbott.com>,   "Graham C Thornton" <graham.thornton_at_ln.ssw.abbott.com> wrote:
> 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
>

OR (since the oracle date format is stored to the second) select ..., (date1 - date2) * 86400 diff, ... from ...

Providing that date1 and date are in oracle's date format. HTH
   James
> 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).
> >>>
> >>
> >
> >
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Mon Apr 19 1999 - 16:49:27 CDT