CurrentDate - 1 Slowdown

[spentmiles]

I am attempting to design a report that pulls yesterday's data from the db. Part of the selection criteria is select all records where the date field is equal to (CurrentDate - 1). This makes the reports run unacceptably slow. Does anyone know a better way to select yesterday's data? BTW: The date field is indexed.

 

[Naith]

You're finding this because CurrentDate-1 is not passing to the database. Replace CurrentDate-1 with the CDate or DateAdd function so that the date clause passes to the SQL.

Naith

 

[synapsevampire]

For the best results here, please post what you have in the record selection formula, Crystal version, and the database type and version.

What Naithh alludes to is using the Database->Show SQL Query to make sure that you're passing to the database what you have in the Report->Edit Selection Formula->Record

It should be something like:

{table.datefield} = currentdate-1

If it still doesn't pass, try:

(
{table.datefield} >= cadtetime(year(currentdate-1),month(currentdate-1),day(currentdate-1),0,0,0)
and
{table.datefield} <= cadtetime(year(currentdate-1),month(currentdate-1),day(currentdate-1),23,59,59)

I have a FAQ in this forum which speaks of optimizing SQL in the record selection formula which will further help you if you cannot get this working.

-k