Several options.
You could probably do something inventive with sign and decode
You might want to use the seconds since midnight format
select to_char(sysdate,'SSSSS') from dual gives the seconds since
midnightselect .. from .. where rdate between
trunc(sysdate-decode(sign(to_char(sysdate,'SSSSS')-(20*60*60)),0,-1,-1,-1,1,
0,0))+(18/24)
andtrunc(sysdate-decode(sign(to_char(sysdate,'SSSSS')-(20*60*60)),0,-1,-1,-1
,1,0,0))+(22/24)but your logic isn't clear to me.You can also use a case
statement in a where clause. Alternatively you could write two pl/sql
functions that take a date as an argument (sysdate) and calculate the two
bounds (the lower bound and the upper bound) In the function you can have
your logic.create or replace lower_date(date_low in date) returns
datebeginif ...return myDate;end;/select .. from .. where rdate between
lower_date(sysdate) and upper_date(sysdate);Jim
"Don A." <dba_at_direct.ca> wrote in message
news:qiaa4vg9p5pjg95mcfa2vu3m4lg2j9kmqs_at_4ax.com...
> I had the current time check, but how do I incorporate that into the where
> clause?
>
> I have (from a programmatic stand-point - more or less):
>
> if (TO_CHAR(sysdate,'hh24:mi:ss') > ('20:00:00'))
> then
> RDATE between ('round(sysdate) 18:00:00','DD-MM-YY hh24:mi:ss")
> and ('round(sysdate) 22:00:00','DD-MM-YY hh24:mi:ss")
> else
> RDATE between ('round(sysdate-1) 18:00:00','DD-MM-YY hh24:mi:ss")
> and ('round(sysdate-1) 22:00:00','DD-MM-YY hh24:mi:ss")
> end
>
> Obviously the above wouldn't work, but it's basically what I want to do,
but
> can't figure out how to get the above concept into the where clause.
>
> My mind just can't wrap around this for some reason.
>
> Any more help would be appreciated.
>
> Thanks,
>
> Don
>
> On Sat, 08 Feb 2003 15:59:35 GMT, "Jim Kennedy"
> <kennedy-downwithspammersfamily_at_attbi.com> wrote:
>
> >sysdate is the current date and trunc(sysdate) will give you the time at
> >midnight. Fractions are parts of a day (eg trunc(sysdate)+.5 is today at
> >noon).
> >Jim
> >"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?
> >>
> >> Any help, pointers, suggestions appreciated.
> >>
> >> Thanks,
> >>
> >> Don
> >
>
Received on Sat Feb 08 2003 - 11:32:06 CST
