below is my command. the default parameters for p1 and p2 are 9/9/2999, 9/9/2999
select id, last_name, start_date, city
from employee
where start_date between to_date(to_char({?p1},'YYYY/MM/DD'),'YYYY/MM/DD') and
to_date(to_char({?p2},'YYYY/MM/DD'),'YYYY/MM/DD')
here is the formula i am using for selecting the records.
(
if {?p1} <> date(2999,9,9) then
{Command.START_DATE} >= {?p1} else
if {?p1} = date(2999,9,9) then
{Command.START_DATE} >= dateserial(year(currentdate),month(currentdate)-1,1)
)
and
(
if {?p2} <> date(2999,9,9) then
{Command.START_DATE} < {?p2} + 1 else
if {?p2} = date(2999,9,9) then
{Command.START_DATE} < dateserial(year(currentdate),month(currentdate),1)
)
the problem is according to the data in the table below i have changed the system date to oct 1st and ran the report so that i should get those 3 columns in sep month but it is not giving me those values. when i manually enter the parameters for those start and end dates of last month i am able to get the values but by default even after setting the parametes to take the last months dates it is not returning the values.
start_date
10 ram ram 9/1/2007 10/1/2007 123.0
10 ram ram 9/2/2007 10/1/2007 123.0
10 ram ram 9/8/2007 10/1/2007 123.0
01 Jason Martin 7/25/1996 7/25/2006 1234.5
02 Alison Mathews 3/21/1976 2/21/1986 6661.7
Thanks...