Optimizing Record selection on datetime fields

[schurers]

Parameters have been setup for Start and End Date for the user to enter the date as yyyy/mm/dd. The database field is actually a datetime field. I created a formula @trndate
converting the datetime to just a date thru this formula: date({APTRN.TRNDTTM}). My selection criteria is {@trndate} in {?StartDate} to {?EndDate}

The selection criteria for the dates are not being processed on the server side. I read an article that I could create the following sql expression:
//@Date
TO_CHAR("TABLE"."DATE_FIELD",'MM/DD/YYYY') but To_CHAR
is not a recognized built-in function. What function do I use instead?

CRXI 11.5
Db Type: OLEDB (ADO)
Sql server

 

[Madawc]

I doubt you can improve on

{@trndate} in {?StartDate} to {?EndDate}

Choose Database > Show SQL Query. Probably it has translated your selection into SQL.

In my own reports, I have sometimes used SQL for date difference, with a big gain in speed. In these cases the original selection was not being translated into SQL, meaning that my own machine was doing it rather than delegating the task to the server, which is much faster.


Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[lbass]

Using an Oracle database, the following works as a SQL expression to convert to a date:

trunc("table"."datetime")

Not sure whether that is available to you.

-LB