"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
