DB2 Date Range in Crystal Reports Command

I am helping fix a Crystal Report that uses a command for the data source.  The database is DB2

The command uses 2 date parameters for the begin and end dates of a range for record selection.

The current report runs correctly but when they schedule the report in Crystal Enterprise, they have to reschedule it or change the parameters since they want last month.  They also want to be able to run it ad hoc for a date range they select.

The current command is like
SELECT table1.field1, table1.field2
FROM table1
WHERE table1.datefield BETWEEN  {?begdt} AND {?enddt}

with this command it takes 45-60 seconds to run

I modified it to


SELECT table1.field1, table1.field2
FROM table1
WHERE
(
    {?begdt} = '1990-01-01'
    AND
    table1.datefield BETWEEN
      ((CURRENT DATE - (DAY(CURRENT DATE) - 1) DAYS ) - 1 ) MONTH)
      AND
     (CURRENT DATE - (DAY(CURRENT DATE)) DAYS)
)
OR
(
table1.datefield BETWEEN  {?begdt} AND {?enddt}
)
\with this it takes 3 minutes to run when the 01-01-1990 date is entered as the parameter.  if 1 Mar - 31 Mar 2012 is entered as the values then it take the same as the original

I tried adding variables to the command so the date is calculated 1 time rather than for each record.  The command actually has 2 subselects that also to the same date range so the date range is calculated 3 times for each record.

I tried

DECLARE begdt date;
DECLARE enddt date;
SET begdt = '01-01-1990';
Set enddt = '01-01-1990';

SELECT table1.field1, table1.field2
FROM table1
WHERE
(
    {?begdt} = '1990-01-01'
    AND
    table1.datefield BETWEEN
      begdt
      AND
     enddt
)
OR
(
table1.datefield BETWEEN  {?begdt} AND {?enddt}
)

I get unexpected token begdt as the error

If I don't use begdt or enddt in the SQL  just add the declaration and SET, it is parsed and accepted and runs the report.

I tried adding BEGIN END around the code, same result

WHat am I missing about DB2 procedures?
Or is there an easier way to get the date ranges I want?


mlmcc