Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with date query - simple?

Re: Problem with date query - simple?

From: Paul Dixon <paul.gp.dixon_at_bttinnedham.com>
Date: Mon, 10 Feb 2003 12:35:16 -0000
Message-ID: <b286ms$gof$1@pheidippides.axion.bt.co.uk>

"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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US