Problem with Selection formulae

[ddrake]

Hi!
Using Crystal 8.5 hitting a SQL2K db. Havin' an issue with the selection formulae. Need to pull out 90 days of cases (it's a Remedy database), but only those created between midnight and 5am. This is my formulae:

({HPD:HelpDesk.Create Time} in Aged0To30Days OR
{HPD:HelpDesk.Create Time} in Aged31To60Days OR
{HPD:HelpDesk.Create Time} in Aged61To90Days) and
(Hour ({HPD:HelpDesk.Create Time}) in [0, 1, 2, 3, 4, 5])

Create Time is a DateTime field. When I check the formulae, it says that "a string is required here" and puts the cursor in front of the Aged0To30Days function...

Thank you!!

 

[crystalreporting]

Try;

{HPD:HelpDesk.Create Time} in in CurrentDateTime to CurrentDateTime - 90 and
Hour({HPD:HelpDesk.Create Time}) in [0, 1, 2, 3, 4, 5]


Peter Shirley

http://www.linkcorp.com

 

[lbass]

You need to to use hour(({HPD:HelpDesk.Create Time}) in [0, 1, 2, 3, 4] if you want to exclude times greater than 5:00 as in 5:45:30, etc. This will return times through 4:59:59. If you really need to include 5:00:00 then I think you should use:

time({HPD:HelpDesk.Create Time}) in time(00:00:00) to time(05:00:00)

-LB

 

[synapsevampire]

Although the above solutions are good in theory, they probably won't offload the processing to the database (Check the Database->Show SQL Query). CR 9 might, CR 8.5 won't.

You're better served to build a SQL Expression which returns the hour, and use that in the Record Selection Formula.

Probably something like:

CAST(datepart(hour, {table.date}) AS int)

-k