Re: Date Comparisons

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/08/30
Message-ID: <34097704.21718830@newshost>#1/1

On 29 Aug 1997 17:44:11 GMT, Ron Joffe <rjoffe_at_er4.eng.ohio-state.edu> wrote:

>I am trying to compare the system date (sysdate) and other date field (old_date).
>
>If I do a :
>SQL> SELECT sysdate - old_date from table;
>I get a Julian Date back: something like :
>2.345345
>I would like to convert that to an hour minute sec type format.
>
>I tried to use the TO_DATE and TO_CHAR using the julian date operator, but it only handles full days, and does not process the devmal part of the day.
>
>Any help would be appreciated.
>
>Thanks,
>
>Ron
>
>

Its not a julian date, its the number of days between the two dates, in this case, there were 2.3 days between the two.

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.

>
>
>
>
>

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 Sat Aug 30 1997 - 00:00:00 CDT