Promt Performance Issue

[Former Member]

Hi BO Experts,

DIM_DATE.CAL_DATE is a database field (defined as Timestamp) which has a values

3/14/2003 12:00:00

3/15/2003 12:00:00, etc...

Below is the syntax for predefined condition:

DIM_DATE.CAL_DATE  BETWEEN  @Prompt('1.Select Start Date:','D',,mono,free) AND @Prompt('2.Select End Date:','D',,mono,free)

It throws me an error saying that Timestamp (DIM_DATE.CAL_DATE) cannot be compared with DATE ("D" (Calendar from WEBI)).

Many of us will suggest to do Trunc(), CAST() on DIM_DATE.CAL_DATE to remove Time part from DIM_DATE.CAL_DATE.

Then the code will become

CAST(DIM_DATE.CAL_DATE AS DATE)  BETWEEN  @Prompt('1.Select Start Date:','D',,mono,free) AND @Prompt('2.Select End Date:','D',,mono,free)

As per my knowledge it not a best practice to have SQL Functions on the left handside of the Operator (Between, Inlist, Equal,etc..).

Is there any other alternative?

-Thanks,

DEEPU

[Former Member · Accepted solution]

Deepu,

You are correct about the performance hit of a function nullifying an indexed column.

Your best bet is to add an extra column as datetime format in your DIM_DATE table.

Either that or you can use a deterministic function and create an index on it. Details are in Books On Line. This page may be of use if this is the route you take - http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/670fd1c5-5990-4ab4-96d6-fe72a...

Regards,

Mark

[Former Member]

Hi,

Try with SubString() and it may help you.

Cheers,

Suresh Aluri.

[Former Member]

before both the prompts use to convert to_date() and compare

may help...

Thanks,

Ganesh