How can I ensure server-side record selection on DateTime fields?

[Former Member]

I'm having a problem getting reports to use DateTime fields in record selection formulas. I can see the references in the record selection formula, but when I try to view the resulting SQL, these references seem to be dropped. I need a way to be sure such DateTime field references are passed to the underlying database for performance. The majority of our ~200 reports support record selection based on DateTime fields so this is a big deal for us.

I'm using Crystal Reports IX R2 SP6 and Microsoft SQL Server 2005 (SQL Native Client driver).

Has anyone successfully dealt with this issue?

[abhilash_kumar · Accepted solution]

Hi Ken,

If you use CR specific functions to convert dates, extract parts of the date like Year, Month etc, then such filters are Not converted to where clause.

Create a SQL Expression field that makes use of equivalent database functions and use these fields in the Record Selection Formula to make sure such filters are passed back to the database.

-Abhilash

[Former Member · Accepted solution]

Thanks for the reply Abhilash.

My record selection formulas do not refer to date functions at all. We're talking basic stuff here:

{TABLE.COLUMN}>=#2013-01-01 00:00:00# and {TABLE.COLUMN}<=#2013-12-31 23:59:59#

(or equivalent formula representing the date values involved).

I've read through the other posts that suggest using a SQL Expression but that method does not seem to be reliable with respect to making sure the formula makes it into the resulting SQL.

[abhilash_kumar · Accepted solution]

This is fairly simple enough and should be passed back to the database for processing.

I tested something similar with a SQL Server database using the Native Client and it works just fine.

Note that if you're reporting against a SQL Query (Command Object) or a Stored Proc then the record selection formula is Not passed back to the database.

-Abhilash

[Former Member · Accepted solution]

If I change my date constants in the example above with SQL Expressions, the SQL does reflect the formula. My problem now shifts to how to go about setting such SQL Expressions at run-time through the RAS model. Any ideas on that?

[Former Member · Accepted solution]

Conversely if I change my {TABLE.COLUM} to a SQL Expression converting the DateTime value to a string, I can then use that expression in my record selection formula comparing against other string formulas and the all make it into the SQL.

Abhilash, thanks for your help!