Skip to Content
0
Jan 18, 2011 at 01:44 AM

Date Format in Record Selection

367 Views

Hello,

I have a problem where the Crystal Report is not returning all the rows that meet the record selection criteria because I believe there is a problem with the date format.

My record selection criteria is:

( {EMPOS.POS_STARTC} <= {@EB_EDATE} and {EMPOS.POS_ENDD} >= {@EB_SDATE} ) or
    IsNull({EMPOS.POS_ENDD} )

where EB_EDATE and EB_SDATE are formula fields with the values Date(2011, 12, 31) and Date(2011, 01, 01) respectively which returns the format of DD/MM/YYYY used by the FinanceOne (F1) ERP application that we run the report from. Both formula fields are overriden by the run parameters in the same date format in F1.

If I were to run the SQL below in a query tool like Toad, it returns a row where pos_startc is 1/01/2011 and pos_endd is null, that is not included in the report.

select * from empos where det_numbera = '01285'
                      and ( (pos_startc <= '20111231' and pos_endd >= '20110101') or pos_endd is null )

1. Why is Crystal Reports excluding this row when the date format is different?

2. How can I change the criteria (or format) to include the one of many other rows missing?