Thanks Thomas that was useful - I don't have to recode my Sybase code!
Thomas Kyte wrote in message <371c3245.855750_at_192.86.155.100>...
>A copy of this was sent to "Paul Wiles" <john_at_adzi.com>
>(if that email address didn't require changing)
>On Mon, 19 Apr 1999 14:31:06 +0100, you wrote:
>
>>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.
>>
>>Surely there must be a simpler way??
>
>to get the time in seconds between 2 dates d1 and d2 you need to:
>
>select (d2-d1)*24*60*60 from T
>
>where did 'SSSSS' come into play? When you subtract 2 dates, you get the
>difference in floating point number in days. multiply by 24 to get hours.
>multiply by 60 to get minutes. mulitply by 60 again to get seconds. The
>difference between 2 dates is a fractional number inclusive of the seconds.
>
>If you really want 'datediff' in your database, you can just do something
like
>this:
>
>SQL> create or replace function datediff( p_what in varchar2,
> 2 p_d1 in date,
> 3 p_d2 in date ) return number
> 4 as
> 5 l_result number;
> 6 begin
> 7 select (p_d2-p_d1) *
> 8 decode( upper(p_what),
> 9 'SS', 24*60*60, 'MI', 24*60, 'HH', 24, NULL )
> 10 into l_result from dual;
> 11
> 11 return l_result;
> 12 end;
> 13 /
>
>Function created.
>
>
>Now, i just create a view to demonstrate with:
>
>SQL> create or replace view temp_view
> 2 as
> 3 select to_date('01-JAN-1999 12:02:04', 'dd-mon-yyyy hh24:mi:ss' ) d1,
> 4 to_date('15-MAR-1999 01:34:23', 'dd-mon-yyyy hh24:mi:ss' ) d2
> 5 from dual
> 6 /
>View created.
>
>
>
>
>SQL>
>SQL> select datediff( 'ss', d1, d2 ) seconds from temp_view;
>
> SECONDS
>----------
> 6269539
>
>SQL> select datediff( 'mi', d1, d2 ) minutes from temp_view;
>
> MINUTES
>----------
>104492.317
>
>SQL> select datediff( 'hh', d1, d2 ) hours from temp_view;
>
> HOURS
>----------
>1741.53861
>
>
>>
>>
>>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).
>>>>
>>>
>>
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Service Industries
>Reston, VA USA
>
>--
>http://govt.us.oracle.com/ -- downloadable utilities
>
>---------------------------------------------------------------------------
-
>Opinions are mine and do not necessarily reflect those of Oracle
Corporation
Received on Mon Apr 19 1999 - 10:42:35 CDT
