SELECT TO_CHAR(t.startdate,'hh24:mi:ss') AS starttime,
TO_CHAR(t.finishdate,'hh24:mi:ss') AS finishtime, LPAD(taskid,5),
startdate, finishdate
FROM TASK t
WHERE
TO_DATE('08/21/2004','mm/dd/yyyy') >= TRUNC(t.startdate)
AND TO_DATE('08/21/2004','mm/dd/yyyy') <= TRUNC(t.finishdate)
AND TO_CHAR(TO_DATE('07:00:00','hh:mi:ss'),'hh24:mi:ss') >=
TO_CHAR(t.startdate,'hh24:mi:ss')
AND TO_CHAR(TO_DATE('07:30:00','hh:mi:ss'),'hh24:mi:ss') <=
TO_CHAR(t.finishdate,'hh24:mi:ss')
nevermind.... I got it
On Wed, 18 Aug 2004 13:45:54 -0400, Greg Morphis <[EMAIL PROTECTED]> wrote:
> Anyone have any idea how to go about doing this?
>
>
>
> >This is irritating me.. it seems like it should work but isnt.
> >
> >I have a query.. the base query
> >
> >SELECT TO_CHAR(t.startdate,'hh24:mi:ss') AS starttime,
> >TO_CHAR(t.finishdate,'hh24:mi:ss') AS finishtime, taskid,
> >startdate, finishdate
> >FROM TASK t
> >
> >returns
> >
> >STARTTIME FINISHTIME LPAD(TASKID,5) STARTDATE FINISHDATE
> >07:00:00 18:00:00 309D2 8/6/2004 7:00:00 AM 8/10/2004 6:00:00 PM
> >09:00:00 14:00:00 30A98 8/1/2004 9:00:00 AM 8/5/2004 2:00:00 PM
> >09:00:00 12:30:00 30AB7 8/2/2004 9:00:00 AM 8/5/2004 12:30:00 PM
> >10:00:00 18:00:00 30BEB 8/9/2004 10:00:00 AM 8/10/2004 6:00:00 PM
> >07:00:00 08:00:00 6E61F 8/21/2004 7:00:00 AM 8/22/2004 8:00:00 AM
> >
> >
> >I shrunk the taskid field down to hopefully make it more legible..
> >
> >What I'm trying to do is pass a date a time return rows that fall
> >within that date/time.. it HAS to check the date and time seperately..
> >
> >So tacking on the date part... no problem
> >
> >SELECT TO_CHAR(t.startdate,'hh24:mi:ss') AS starttime,
> >TO_CHAR(t.finishdate,'hh24:mi:ss') AS finishtime, LPAD(taskid,5),
> >startdate, finishdate
> >FROM TASK t
> >WHERE
> >TO_DATE('08/21/2004','mm/dd/yyyy') >= TRUNC(t.startdate)
> >AND TO_DATE('08/21/2004','mm/dd/yyyy') <= TRUNC(t.finishdate)
> >
> >this returns 1 row as it should..
> >07:00:00 08:00:00 6E61F 8/21/2004 7:00:00 AM 8/22/2004 8:00:00 AM
> >
> >but now I have to check a time to make sure it doesnt fall within that range..
> >
> >to me it seems
> >AND TO_CHAR(TO_DATE('07:00:00','hh:mi:ss'),'hh24:mi:ss') >= t.starttime
> >AND TO_CHAR(TO_DATE('07:30:00','hh:mi:ss'),'hh24:mi:ss') <= t.finishtime
> >should work..
> >this returns
> >07:00:00 and 07:30:00 and it should compare to starttime and
> >finishtime.. but it's not working.
> >
> >Can someone offer assistance?
> >
> >Thanks!
>
>
[Todays Threads]
[This Message]
[Subscription]
[Fast Unsubscribe]
[User Settings]
[Donations and Support]
- SQL date/time issues... Greg Morphis
- Re: SQL date/time issues... Greg Morphis
- Greg Morphis