Re: SQL or PL/SQL Help of Just Plain Help!! (newbie)

From: <pberetta_at_my-deja.com>
Date: Thu, 20 Jan 2000 14:06:52 GMT
Message-ID: <8674pi$286$1_at_nnrp1.deja.com>

Robin,
If you decide to go with Bob's solution, you might want to modify it slightly, to:
select FLOOR(a.TimeDiff) Days,

      FLOOR(MOD(a.TimeDiff, 1) * 24) Hours,
      ROUND(MOD(a.TimeDiff * 24, 1) * 60) Mins

from (select to_date(to_char(db1.daterevised,'mm-dd-yyyy hh24:mi'), 'mm- -yyyy hh24:mi') -
to_date(to_char(db1.dateentered,'mm-dd-yyyy hh24:mi'), 'mm-dd-yyyy hh24:mi') TimeDiff

      from db1) a;
This eliminates possible problems with TO_DATEing a DATE. Hope this helps,
Paul

Bob - thanks again for your help with my DBMS_SQL problem!

In article <RiBh4.63$7R.821_at_news.colt.net>,   "Bob Bain" <bob.bain_at_terra-nova.e-mail.com> wrote:
> Try This
>
> select FLOOR(a.TimeDiff) Days,
> FLOOR(MOD(a.TimeDiff, 1) * 24) Hours,
> ROUND(MOD(a.TimeDiff * 24, 1) * 60) Mins
> from (select to_date(db1.daterevised, 'mm-dd-yyyy hh24:mi') -
> to_date(db1.dateentered, 'mm-dd-yyyy hh24:mi') TimeDiff
> from db1) a
> /
>
> hth
>
> Bob
>
> Boscia <rwboscia_at_worldnet.att.net> wrote in message
> news:B4ABE5F2.A7D%rwboscia_at_worldnet.att.net...
> > I am running Oracle Reports and attempting to write SQL to pull
data from
> > tables...
> >
> > I have fields of data that are DATE (mm-dd-yyy hh:mm).
> >
> > Example: db1.ticketno db1.dateentered db1.daterevised
> > 32 01-02-2000 00:00 01-05-2000
11:59
> >
> > How can I subtract one date from another and come up with a cycle
time
> > report that would show the amount of time in days, hours and
minutes that
> it
> > took from the time that ticket was entered until it was revised?
> >
> > (If PL/SQL is needed, please go easy on me, since I am not very
familiar
> > with it and no one in my office knows it either!!)
> >
> >
> > Thanks in advance...
> > Robin
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jan 20 2000 - 15:06:52 CET