Re: Oracle Dates in Sqlplus

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/09/18
Message-ID: <342354e0.52144710_at_newshost>#1/1

On 18 Sep 1997 14:20:48 GMT, David Power <powerd_at_ogica.com> wrote:

>Does anyone know how to get the difference between two oracle
>datetimes in minutes using SQL.
>
>David Power.

the difference between 2 dates is some real number representing the number of days between 2 dates. So for example the following shows one way to get days/hours/minutes (depending on how you want to round, I "round" down with trunc)

column a format a15
column b format a15

select to_char(created, 'dd-mon-yy hh24:mi') a,

       to_char(sysdate, 'dd-mon-yy hh24:mi') b,
       trunc(sysdate - created) days,
       trunc(mod((sysdate-created)*24, 24 )) hours,
       trunc(mod((sysdate-created)*24*60, 60)) minutes

from all_users
where rownum < 10
/

This shows the number of days, hours, minutes between the creation date of a user and today.

to get just minutes you would:

select (date1-date2) * 24 * 60
from T

multiply by 24 to get hours, by 60 to get minutes.

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 18 1997 - 00:00:00 CEST