Using a date parameter for date/time field

[Chats]

Hi,

I have a date time field in an Oracle database, I am building a report in Crystal 8.5.

I want to include a user prompt on the date time field, but have the user only enter a date.

I have set my record selection formula to

{?Date Range} = date({WORK.DATE})

This works fine, but seems to shift the record selection to Client side (when I view SQL statement the record selection is not shown, so I assume all records are being retrieved and then Crystal is selecting on date)

Is there any way to convert a date/time to date within a record selection formula, and leave the record selection server side? This has obvious performance advantages.

Thanks for your advice

Ade

 

[lbass]

I am not absolutely sure that this gets translated to the SQL, but go to report options and select convert date-time field to date.

-LB

 

[Digitalcandy]

Take the "date" function out of your formula and try it.

{?Date Range} = {WORK.DATE}

 

[amatson]

I had this same situation and used an Oracle function in the sql where clause:

...AND TABLE.DATE_FIELD BETWEEN TO_DATE('15-10-2002','DD-MM-YYYY') AND TO_DATE('06-11-2002','DD-MM-YYYY');

 

[Chats]

amatson,

Thanks I understand this bit, but how do I replace the hard coded dates with a prompt, and can I do this without the processing then going client side?

Ade

 

[AnnetteH]

I created a date parameter (DateP) and checked Range Values. Then I put that parameter in the selection expert as shown.
{ORDERS.ORDER_DATE} = {?DateP}

The SQL query that resulted was the following, and the user is only prompted to enter a date, not a datetime.
SELECT "ORDERS"."ORDER_ID", "ORDERS"."ORDER_AMOUNT",
"ORDERS"."CUSTOMER_ID", "ORDERS"."EMPLOYEE_ID",
"ORDERS"."ORDER_DATE"
FROM "XTREME"."ORDERS" "ORDERS"
WHERE ("ORDERS"."ORDER_DATE">=
TO_DATE ('12-06-2000 00:00:00', 'DD-MM-YYYY HH24:MI:SS') AND &quot;ORDERS&quot;.&quot;ORDER_DATE&quot;<
TO_DATE ('13-06-2001 00:00:00', 'DD-MM-YYYY HH24:MI:SS'))

So Crystal automatically set up the query correctly and the selection criteria was passed to the database.

I'm using Crystal 9, so perhaps this doesn't work as well in 8.5.

 

[rosemaryl]

Dear Chats,

This works exactly the same in CR 8.5.

If your parameter is a type of Date and the field is a type of DateTime then Crystal will automatically adjust and it should show in the SQL Query.

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.