Re: Problem with date query

From: Martin Burbridge <pobox002_at_bebub.com>
Date: Wed, 26 Feb 2003 03:02:30 GMT
Message-ID: <Xns932DDFA7F991Fpobox002bebubcom@204.127.204.17>

Don A. <dba_at_direct.ca> wrote in
news:qiaa4vg9p5pjg95mcfa2vu3m4lg2j9kmqs_at_4ax.com:

> 
> 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. 
> 
> 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')
>>>
>>>
>>> Any help, pointers, suggestions appreciated.
>>>
>>> Thanks,
>>>
>>> Don
>>

>

Hello Don,

Like Jim said you just need trunc and date math.

1 is a day so 1/24 is an hour, with that you can get the start and end time.

Here's an example just replace to_date(:datestr) with sysdate to make it dynamic.

SQL> alter session set nls_date_format = 'MM-DD-YYYY HH24:MI';

Session altered.

SQL> var datestr varchar2(20)
SQL> exec :datestr := '02-25-2003 21:30'

PL/SQL procedure successfully completed.

SQL> select to_date(:datestr) mysysdate,

  2     trunc(to_date(:datestr)-(22/24)) + 18/24 start_time,
  3     trunc(to_date(:datestr)-(22/24)) + 22/24 end_time

4 from dual;

MYSYSDATE START_TIME END_TIME

---------------- ---------------- ----------------

02-25-2003 21:30 02-24-2003 18:00 02-24-2003 22:00

SQL> exec :datestr := '02-25-2003 22:30'

PL/SQL procedure successfully completed.

SQL> / MYSYSDATE START_TIME END_TIME

---------------- ---------------- ----------------

02-25-2003 22:30 02-25-2003 18:00 02-25-2003 22:00

So then your where clause would be

where rdate > trunc(sysdate-(22/24)) + 18/24 and rdate < trunc(sysdate-(22/24)) + 22/24

Hth

Martin Received on Tue Feb 25 2003 - 21:02:30 CST

Re: Problem with date query - simple?