Convert Date-time to Date in the Record selection

[easaw]

I am using Crystal Reports version 10.0 and Oracle Server Connection (Oracle native driver connection). I have a parameter named Starting_date and prompts for one value of a date. This parameter has been set as a Date Value Type. In the Select Expert I specify the criteria for the Record Selection where the Database Date field is supposed to find records with an exact match to the parameter value passed in
for e.g. {Table1.START_DATE} = {?Starting_date},

but on executing the report the SQL from the menu Database-> Show SQL Query... shows that the Database field is compared with a Date Range of a complete day whereas my select expert does not have a comparison with a date range.

If a parameter value of 05/05/2004 is passed through the SQL Query shows as the following

WHERE "Table1"."RANK"<51 AND
"Table1"."PERIOD_WINDOWS_NUM"<3 AND
"Table1"."LEVEL_3_ID"=18 AND
("Table1"."START_DATE">=TO_DATE ('05-05-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"Table1"."START_DATE"<TO_DATE ('06-05-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

In versions previous to Crystal Reports 9.0 there was an option to Convert Date-time fields to Date and this problem used to be taken care of. Since in Crystal Report version 10.0 this option is unavailable from the File->Report Options menu, I would like to know how to handle this issue.

 

[Naith]

Amend your example to:

{Table1.START_DATE} = Date({?Starting_date})

Naith

 

[easaw]

Thanks for the response. Using the following variation
{Table1.START_DATE} = Date({?Starting_date}) it did come up with the same results in the SQL Query.
("Table1"."START_DATE">=TO_DATE ('05-05-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"Table1"."START_DATE"<TO_DATE ('06-05-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

 

[easaw]

Thanks for the response. Using the following variation
{Table1.START_DATE} = Date({?Starting_date}) it did come up with the same results in the SQL Query.
("Table1"."START_DATE">=TO_DATE ('05-05-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND
"Table1"."START_DATE"<TO_DATE ('06-05-2004 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

 

[Naith]

Hold up a second - I've been reading posts with one eye again.

What's wrong with the way the SQL is interpreting this? I appreciate you're expecting Date1 = Date2, but the way your SQL is coming out will experience no more detriment than if it had used the Date1 = Date2 approach. The same indices will still get used by both, if applicable.

The difference in the generated SQL owes to the updated database driver that you're using with 10, but I see no reason why this change should interfere with the rows you're expecting to get returned.

Naith

 

[easaw]

Thanks for the Help Naith.
some reason some time my report fails for certain date parameter. (Failed to open rowset)

 

[Naith]

Failed to open rowset usually pairs up with another error message, and often points to a possible backend problem.

Try running the sql directly against the database and see what happens.

 

[easaw]

Thanks Naith, finally I was able to find out that the errors that I am getting is due to Backend problem. But I still would like to know why the SQL Query display different results in Crystal 8.5 and Crystal 10.0 for the same parameter.

Crystal Report 8.5
"RPT_DUT_RANK_DRUG_LV3"."START_DATE" = TO_DATE ('06-05-2004', 'DD-MM-YYYY')

Crystal Report 10.0
("RPT_DUT_RANK_DRUG_LV3"."START_DATE">=TO_DATE ('06-05-2004 00:00:00',
'DD-MM-YYYY HH24:MI:SS') AND
"RPT_DUT_RANK_DRUG_LV3"."START_DATE"<TO_DATE ('07-05-2004 00:00:00',
'DD-MM-YYYY HH24:MI:SS'))

 

[Naith]

Because you're using different database drivers.

The logic is the same though.