Pass through to SQL on Record Selection with Date 1

[random621]

I've been reading SynapseVampires FAQ on Optimizing SQL Pass through and I'm just not getting it.

I have a report with the following Select Expert at Record level:
({loggerlog.AGNTID} startswith "1" or
{loggerlog.AGNTID} startswith "4")
and
(Date ({loggerlog.STARTTIME}) in LastFullMonth)
and
(Len ({loggerlog.AGNTID}) >= 3)

This results in the following SQL Query:
SELECT `loggerlog1`.`LOGID`, `loggerlog1`.`LOCCHANNEL`, `loggerlog1`.`AGNTID`, `loggerlog1`.`STARTTIME`, `loggerlog1`.`RECLENGTH`
FROM `vdlogger`.`loggerlog` `loggerlog1`
WHERE (`loggerlog1`.`AGNTID` LIKE '1%' OR `loggerlog1`.`AGNTID` LIKE '4%')


I feel like the problem must be that I am not converting the datetime field "STARTTIME" correctly. As always any help is appreciated.

 

[random621]

I forgot to mention, this is on Crystal XI.

 

[lbass]

Is {loggerlog.STARTTIME} a datetime field? You shouldn't need to convert it to a date to have it work with lastfullmonth. If you have some need to convert it, however, try creating a SQL expression using trunc(), as in {%startdate}:

trunc(loggerlog.`STARTTIME`)

Then use the {%startdate} in your record selection formula--it will pass to the SQL. You can also use the length function in a SQL expression {%len} in order to have that clause pass as well:

len(loggerlog.`AGNTID`)

-LB