Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with date query - simple?
"Don A." <DBA_at_direct.ca> wrote in message
news:ie8a4vgrucuoa8babcs8jrkqqc9177t8vk_at_4ax.com...
> Greetings,
>
> I'm trying to select records from a table based on a date field and can't
seem
> to figure out how to set up variable date checking. This select statement
is in
> a view so it needs to be able to determine the current time and then
request
> either today's data or yesterday's. (All of this is on 8.1.7.2.5 under
W2K)
>
> Basically I want all records between a certain time (say 6 PM and 10 PM)
from
> the nearest current time.
>
> That is, if it's currently before 10 PM I want the data from last night,
> otherwise grab the data from tonight.
>
> I can put in a set date and time manually :
>
> i.e. ... where RDATE between ('08-FEB-03 18:00:00','DD-MM-YY hh24:mi:ss')
> and ('08-FEB-03 22:00:00','DD-MM-YY hh24:mi:ss')
>
> But obviously it means the select statement is static. How can I check the
> current time and then pass this to the where clause so it requests the
> appropriate days data?
Don,
Try .... WHERE RDATE between TRUNC(sysdate-22/24)+18/24 and TRUNC(sysdate-22/24)+22/24 .
TRUNC(sysdate-22/24) will give you the date you want, with it's time component removed. 18/24 and 22/24 are 6 pm and 10 pm respectiveley.
Paul Dixon Received on Mon Feb 10 2003 - 06:35:16 CST