Re: Newbie Q: Calculating Difference Between 2 Dates

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Fri, 31 Jul 1998 18:50:39 GMT
Message-ID: <01bdbcc3$e36c1d80$a504fa80@mndnet>

In SQL*Plus, if date2 is higher than date1, you can simply do

select trunc(date2) - trunc(date1) from dual;

This will give you the difference between those 2 dates in number of days. If the dates do not have the actual timestamp i.e. timestamp is 00:00:00, then you simply multiply the number of days by appropriate number for hours/seconds per day etc. to get the proper results.

If the date portion is present then you will have to do some more math as given below:

For example, you will get seconds if you do something like this:

select ( (24*60*60 - to_char(date1, 'sssss'))

        + 24*60*60*(trunc(date2) - trunc(date1))
        + (to_char(date2, 'sssss'))
       ) seconds

  from dual;

Format sssss gives you the seconds from midnight up to the timestamp in date1 or date2.

In PL/SQL you can do

num_days_var := trunc(date2) - trunc(date1);

total_seconds_var := ( (24*60*60 - to_char(date1, 'sssss'))

        + 24*60*60*(trunc(date2) - trunc(date1))
        + (to_char(date2, 'sssss'))
       ) ;

Please check to make sure.

Good luck !!!

suresh.bhat_at_mitchell-energy.com

Scott C. <sdcairns_at_mindspring.com> wrote in article <6psvs4$688$1_at_camel15.mindspring.com>...
> I'm looking for the PL/SQL to calculate the difference between 2 dates,
> specifically in # of seconds although I need it in all forms eventually
(#
> days, # hours, etc.).
>
> The equivalent function in Transact-SQL (Sybase/MS SQL) was datediff
(date
> part, date1 , date2). All I can find in PL/SQL is MONTHS_BETWEEN which
> isn't very useful.
>
>
>
>
Received on Fri Jul 31 1998 - 13:50:39 CDT