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]
