Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sql query to find correct time diffrence

Re: sql query to find correct time diffrence

From: <fitzjarrell_at_cox.net>
Date: 26 Dec 2005 06:37:48 -0800
Message-ID: <1135607867.961792.175700@g14g2000cwa.googlegroups.com>

Jack wrote:
> LPAD(TRUNC((((substr(end_time, 1, 2) * 60) + substr(end_time, 3, 2)) -
> ((substr(start_time, 1, 2) * 60) + substr(start_time, 3, 2)))/60) ,2,0)
> || LPAD(MOD((((substr(end_time, 1, 2) * 60) + substr(end_time, 3,
> 2)) - ((substr(start_time, 1, 2) * 60) + substr(start_time, 3, 2))) ,
> 60),2,0)
>
> I am doing this using the above.....
>
> looking for a clean way of doing this...
> Thanks
>
>
> fitzjarrell_at_cox.net wrote:
> > Comments embedded.
> > Jack wrote:
> > > Hi,
> > >
> > > I have a table with 2 columns(varchar) end_time & start_time.
> > > I have to write a query to find the correct diffrence of end_time &
> > > start_time
> >
> > Then why are you asking US to write it?
> >
> > >
> > > eg.
> > > start_time end_time
> > > 0830 1705
> > >
> > > the query should return ... 0835
> > >
> >
> > Last I checked 17 hours and 5 minutes equals 16 hours and 65 minutes
> > ...
> > and I leave the rest up to you.
> >
> > > Thanks
> >
> > You're more than welcome, and I hope your homework gets completed on
> > schedule.
> >
> >
> > David Fitzjarrell

SQL> select * from timetest;

TIME TIME
---- ----
0830 1705
0840 1645
0820 1803

SQL> @timediff
SQL> select
  2 case
  3 when substr(time_in, 3, 2) > substr(time_out, 3, 2) then   4 lpad(to_char((to_number(time_out) - 100 + 60) - to_number(time_in)), 4, '0
')
  5 when substr(time_in, 3, 2) <= substr(time_out, 3, 2) then   6 lpad(to_char(to_number(time_out) - to_number(time_in)), 4,'0')   7 end as timediff
  8 from timetest
  9 /

TIME



0835
0805
0943

David FItzjarrell Received on Mon Dec 26 2005 - 08:37:48 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US