When people enter date criteria they almost always expect to enter just the date value without times.  But most modern databases use a DateTime data type rather than a date.  If your data stores dates with times you have to be sure that you enter your date ranges correctly or you take chance of losing the data for the last day of the range.

The good news is that in most situations CR will make the adjustment automatically, but I found one situation where the adjustment doesn’t happen.  Say you have a transaction date that is a DateTime field, and you are trying to select the transaction for  January.  You might go into the select expert and  add a rule that says  TransactionDate / is between /

And then at this point you type in the two dates as  1/1/2012 and  1/31/2012.  Once you have run the report go into the record selection formula and look at what it says:

{Trans.Date} in DateTime (2012, 01, 01, 00, 00, 00)
to DateTime (2012, 01, 31, 00, 00, 00)

The last three zeros in each value are hour, minute and second.  Since you didn’t specify times for the date time value it will assume that they are all zeros which means midnight.  This criteria will miss all of the records on 1/31 that have a time value that is after midnight, which is essentially the entire last day.  Now some databases don’t store any time values, so all the dates are entered as midnight and there you would not have a problem.  But if you apply the above criteria to a database field that has times in it you will miss the last day of the range.   Go to the Database Menu and select Show SQL Query and you will see that it is using the same dates.

But now go back into the selection formula and change DateTime to Date in both places.  Then remove the last 3 zeros from each value so that the formula looks like this:

{Trans.Date} in Date(2012, 01, 01) to Date(2012, 01, 31)

Save the formula, and now look at the SQL Query.  The times are still zeros but you will notice that the second date in the query is one day greater than the date in your formula.  And if you look carefully the comparison used with that date is just the ‘<‘ and not the ‘<=’ comparison.  In other words get all records that are before midnight on the next day after the date you specify.

The same adjustment occurs if you use a Date parameters, or a Date Range parameter in your selection formula.  When Crystal generates the WHERE clause it will add one day to the end date of the range, and use the ‘<‘ comparison.

But if you are using SQL Commands with date parameters you will need to make an adjustment to the SQL. I wrote about one option here where you remove the time value from the database field before you apply the criteria.   The alternative would be to add one day to the parameter in the SQL, and then use the ‘<‘ comparison the way CR does it.