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: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Mon, 26 Dec 2005 15:49:21 -0800
Message-ID: <_KidnTlSseOOHC3eRVn-pQ@comcast.com>

"Jack" <amitkr.3_at_gmail.com> wrote in message news:1135610694.617889.248370_at_g44g2000cwa.googlegroups.com...
> Kool...
>
> Thanks
>
> fitzjarrell_at_cox.net wrote:
> > 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
>

And why are we storing numbers in character fields? Not a good idea. Jim Received on Mon Dec 26 2005 - 17:49:21 CST

Original text of this message

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