Date Parameter Problem 1

[Wayne79]

I have created a parameter for my report named CutOffDate which has been set to a date field.

I set a record selection criteria to be the tables date fields equal to the parameter.

My data is in DB2.

When I run the report I get no records.

When I look at the sql statement used by Crystal reports (v8.5) This is what I see:

SELECT
DR_STMT_REPORT."STATEMENT_ID", DR_STMT_REPORT."STATEMENT_DATE", DR_STMT_REPORT."VENDOR_ID",
VENDOR."VENDOR_ID", VENDOR."NAME", VENDOR."ADDRESS_1", VENDOR."ADDRESS_2", VENDOR."CITY", VENDOR."PROVINCE", VENDOR."POSTAL_CODE",
DRIVER."DRIVER_ID", DRIVER."NAME", DRIVER."ADDRESS_1", DRIVER."ADDRESS_2", DRIVER."CITY", DRIVER."PROVINCE", DRIVER."POSTAL_CODE", DRIVER."PHONE",
{fn USER()}
FROM
"DR_STMT_REPORT" DR_STMT_REPORT INNER JOIN "VENDOR" VENDOR ON
DR_STMT_REPORT."VENDOR_ID" = VENDOR."VENDOR_ID" INNER JOIN "DRIVER" DRIVER ON
VENDOR."VENDOR_ID" = DRIVER."DRIVER_EMPLOYMENT"
WHERE
VENDOR."VENDOR_ID" = 'THOMD' AND
DR_STMT_REPORT."STATEMENT_DATE" >= {ts '2005-08-28 00:00:00.00'} AND
DR_STMT_REPORT."STATEMENT_DATE" <= {ts '2005-08-28 23:59:59.00'}
ORDER BY
DR_STMT_REPORT."VENDOR_ID" ASC,
DRIVER."DRIVER_ID" ASC

If I execute the query after removing the date field from the where clause I get 4 records. 2 on 8/27/2005 and 2 on 8/28/2005. The fields are date time and the time on all 4 records is 23:59:59.

What am I doing wrong?
What do I need to do to fix it?

 

[Madawc]

I'd change the Crystal record selection to Date({DR_STMT_REPORT.STATEMENT_DATE) = date(2005, 08 28). You could also make it 23:59:59.59 on the original test, but this is simpler. Crystal also has a nasty habit of taking notice of infinitessimal differences.

If that doesn't work, please post the actual Crystal selection statement.

Madawc Williams (East Anglia, UK)

 

[Wayne79]

The sql statement above is the actual Crystal Selection statement. I cut and pasted it from the Database > Show SQL Query... menu.

Editing the record selection formula to date({DR_STMT_REPORT.STATEMENT_DATE}) = date({?CutOffDate}) gave me a "DateTime field required here" at both Date Functions.

Another question...What is the ts in the sql statement and how does it get there?

 

[Madawc]

Sorry, it should be Cdate({DR_STMT_REPORT.STATEMENT_DATE) = Cdate(2005, 08 28).



Madawc Williams (East Anglia, UK)

 

[Wayne79]

Same error. "A date-time is required here.

 

[lbass]

If you make the parameter a date type, then the formula should be:

date({DR_STMT_REPORT.STATEMENT_DATE}) = {?date}

-LB

 

[Wayne79]

Neither
Date({DR_STMT_REPORT.STATEMENT_DATE}) = {?CutOffDate}
nor
CDate({DR_STMT_REPORT.STATEMENT_DATE}) = {?CutOffDate}
worked.

I get the same error. "A date-time is required here."

CDateTime({DR_STMT_REPORT.STATEMENT_DATE}) = {?CutOffDate} gave no error but selected no records. Completely removed the date field from the where clause. I suspect that Crystal was filtering and not using the sql statement to filter.

 

[lbass]

Please check the datatype of {DR_STMT_REPORT.STATEMENT_DATE} and of {?CutOFfDate} and report back.

-LB

 

[Wayne79]

{DR_STMT_REPORT.STATEMENT_DATE} is a date field in the DB2 table

{?CutOFfDate} has a value type of Date.

 

[lbass]

Then the formula should be:

{DR_STMT_REPORT.STATEMENT_DATE} = {?CutOffDate}

If that isn't working (and I don't see why it wouldn't), then try:

{DR_STMT_REPORT.STATEMENT_DATE} >= {?CutOffDate} and
{DR_STMT_REPORT.STATEMENT_DATE} < {?CutOffDate} + 1

-LB

 

[Wayne79]

Using your second formula generates the where clause
DR_STMT_REPORT."STATEMENT_DATE" >= {ts '2005-08-28 00:00:00.00'} AND
DR_STMT_REPORT."STATEMENT_DATE" < {ts '2005-08-29 23:59:59.00'}


Which works! Thank you very much for the help.