>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]
