datatime selection formula

[martaB]

In CR8.5 we used global conversion of datatime to character to prevent CR to rewrite Where-clause.

In CR10 it is not more possible and we have the problem that the query using
Table.Date_From => 'YYYY.MM.YY' and Table.Date_From < 'YYYY.MM.YY+1' is extremaly slow.

How can I convibe CR to generate following where-Clause:
Table.Date_From = 'YYYY.MM.YY'????

 

[synapsevampire]

Rather than use the report GUI to generate the report, you can use a Command, which allows for pasting in SQL (under your connection it should be the first option).

When requesting database optimization tips, please include the database type, version and the connectivity used, otherwise people can only guess.

Also using terchnical terms for things like " we used global conversion of datatime to character" will be helpful. Did you use a SQL Expression, or did you use a View on the database, or? I assume that you mean datetime, but since you typed it that way twice, I'm uncertain.

You should be able to pass the SQL in that fashion by adding a parameter as a type date, then editing the Report->Selection Formulas->Record to state:

{table.date} = {?MyDateParameter}

Please understand that SQL of Table.Date_From = 'YYYY.MM.YY' makes no sense, and that converting a date to a char will likely impede performance.

If you just want to compare to text, use the following in the record selection formula:

{table.date} = totext(MyDate,"yyyy.MM.dd")

-k

 

[martaB]

Using auf Command is not possible because we use native Sybase-Server Driver and in this case it is not supported by CR (this option not exist under connection).

"we used global convertion of datatime to character" - I meant that we used an CR8.5 Reportoption "convert datatime to". This option was removed in CR9.

I cannot compare to text. That is what we have done in CR 8.5, here we used record selection formula
{table.date} = totext(MyDate,"yyyy.MM.dd").
However CR10 complaints that datatime parameter is required at this place.

If I use the formula
{table.date} = {?MyDateParameter}

than CR10 rewrites where-clausel in
{table.date} => {?MyDateParameter} and {table.date} < {?MyDateParameter} +1


And that is what I want to prevent.

 

[synapsevampire]

I don't understand this...

Crystal will never pass SQL of:

{table.date} => {?MyDateParameter} and {table.date} < {?MyDateParameter} +1

I need specifics now:

What type of parameter are you using, a date RANGE parameter?

If so, then the where clause SHOULD generate a < and > clause when the dates aren't the same day.

Anyway, I don't understand why you convert it to a string, if the fields a datetime, use a date parameter against it.
If you inttroduce variables or parameters in a post, please state their type and attributes.

-k

 

[martaB]

surely rewrites Crystal SQL in this case:

http://support.businessobjects.com/library/kbase/articles/c2015619.asp

And I do not need to covert date to char. It was just a workaround to prevent CR from rewriting SQL. And this workaround is not available in CR 10. That is my problem.