Date range 1

[liliya13]

Hi,

I need to select a member within a particular Date Range (for ex, 3 months)

I assume I need something like the following:
select a.subid,a.rec_type,a.fname,a.lname,r.run_date,r.rpt_name,a.g rp,a.cvg_cd,a.descr
from activity a, reports r
where a.subid='&subid' AND
a.rpt_id=r.rpt_id AND
to_char(r.run_date) >= to_char('&r.run_date') AND
to_char(r.run_date) <= to_char('&r.run_date')


Am I right or wrong?
I also wonder if I should I put to_char function as I did on the left hand?

Thanks in advance,

Liliya

 

[carp]

Almost.
I think what you are trying to do would be done with the following:

select a.subid,a.rec_type,a.fname,a.lname,r.run_date,r.rpt_name,a.g           rp,a.cvg_cd,a.descr
from activity a, reports r
where a.subid='&subid' AND
a.rpt_id=r.rpt_id AND
TRUNC(r.run_date) BETWEEN to_date('&run_date1','DD-MON-YYYY') AND to_date('&run_date2','DD-MON-YYYY');

I am assuming from your code that you plan to pass in a subid and two dates. Also, I am using a format like 5-AUG-2004 for the date. If you are using something different, then you will want to change the date format picture I am using.

Let us know if this does it for you or if you need something else.

 

[SantaMufasa]

Liliya,

You have several options available to you. You can use a literal date range or (as in the example you gave), use a displacement (such as 3 months) from a given date. Here are examples of how your WHERE clause might read:

SELECT ... FROM ...
WHERE r.run_date between to_date('01-JAN-2004','dd-MON-yyyy') and
                         to_date('15-FEB-2004');

or
SELECT ... FROM ...
WHERE r.run_date between to_date('01-JAN-2004','dd-MON-yyyy') and
                         ADD_MONTHS(to_date('01-JAN-2004','dd-MON-yyyy'),3);
****************************************************************************

The first example displays information WHERE r.run_date is between two specific dates; the second example, where r.run_date is between a specific date and 3 months from that date.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:20 (05Aug04) UTC (aka "GMT" and "Zulu"), 11:20 (05Aug04) Mountain Time)

 

[liliya13]

Thank you!

I am not sure,however,if I should put upper function in this case (as I have just put below)for substitution variables
OR
it is supported by 'DD-MON-YYYY' format ...because it is implemented in capital letters?

select a.subid,a.rec_type,a.fname,a.lname,r.run_date,r.rpt_name,a.g rp,a.cvg_cd,a.descr
from activity a, reports r
where a.subid='&subid' AND
a.rpt_id=r.rpt_id AND
TRUNC(r.run_date) BETWEEN UPPER(to_date('&run_date1','DD-MON-YYYY')) AND UPPER(to_date('&run_date2','DD-MON-YYYY'));

 

[liliya13]

Thank you!

I am not sure,however,if I should put upper function in this case (as I have just put below)for substitution variables
OR
it is supported by 'DD-MON-YYYY' format ...because it is implemented in capital letters?

select a.subid,a.rec_type,a.fname,a.lname,r.run_date,r.rpt_name,a.g rp,a.cvg_cd,a.descr
from activity a, reports r
where a.subid='&subid' AND
a.rpt_id=r.rpt_id AND
TRUNC(r.run_date) BETWEEN UPPER(to_date('&run_date1','DD-MON-YYYY')) AND UPPER(to_date('&run_date2','DD-MON-YYYY'));

 

[zephan]

Liliya,
I don't think it is what Mufasa intended to tell you. What he wanted to, is : if your range is fixed (eg. 3 months) you can use add_months function instead of two parameters. It is'nt a matter of case. However if you use :
TRUNC(r.run_date) BETWEEN UPPER(to_date('&run_date1','DD-MON-YYYY')) AND UPPER(to_date('&run_date2','DD-MON-YYYY'))
you'll get an error because the UPPER function expects a varchar as argument, and you're giving it a date.
Regards,
Zephan

 

[carp]

zephan is absolutely right!
If you are going to put the UPPER function in here, then you would want to use
BETWEEN to_date(UPPER('&run_date1'),'DD-MON-YYYY')

However, with that said, I would also note that I don't think the UPPER is necessary. A simple experiment shows that Oracle has already anticipated this problem for you:

SQL> select to_date('1-aug-2004','DD-MON-YYYY') from dual;

TO_DATE('
---------
01-AUG-04