Re: Newbie Q: Calculating Difference Between 2 Dates

I had a typo below, sorry for any confusion. It should say It may be simpler to just subtract the dates, withOUT the trunc, the result will be the number of days as the integer portion and the fraction represents the number of seconds (times 86,400). This is the 24*60*60 below. The date minus date will always result in a number. It gets tricky converting this into the date format for printing (Oracle assumes some defaults for dates during conversion).

In article <lorenzen-ya02408000R0308981025200001_at_news.visi.com>, lorenzen_at_tiny.net (James Lorenzen) wrote:

>IT may be simpler to just subtact the dates, with the trunc, the result
>will be the number of days as thje integer portin and the fraction
>represents the number of seconds (times 86,400). This is the 24*60*60
>below. The date minus date will always result in a number. It gets tricky
>converting this into the date format for printing (Oracle assumes some
>defaults for dates during conversion).
>
>James
>
>
>In article <01bdbcc3$e36c1d80$a504fa80_at_mndnet>, "Suresh Bhat"
><suresh.bhat_at_mitchell-energy.com> wrote:
>
>>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.
>>>
>>>
>>>
>>>
>--
>lorenzen_at_tiny.net | Life is complex; it has
> | real and imaginary parts
--

lorenzen_at_tiny.net             | Life is complex; it has
                              |   real and imaginary parts