Re: sql query to find correct time diffrence

From: Jack <amitkr.3_at_gmail.com>
Date: 26 Dec 2005 07:24:54 -0800
Message-ID: <1135610694.617889.248370@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
Received on Mon Dec 26 2005 - 09:24:54 CST