@prompt date to character conversion problem

[Former Member]

Hi All,

System: BO 3.1 SP3

DB: Oracle 10g

I have a data base table which has date(year&month) store in number format eg. 201104 .

I what user to select date from calander, for which i have created @prompt with syntex

@Prompt('1_Select From Date [ex.MM/DD/YYYY]','D',,mono,free,not_persistent).

I have created a filter below which gives error ORA 01722

Date = To_Number(To_Char(@Prompt('1_Select From Date [ex.MM/DD/YYYY]','D',,mono,free,not_persistent),'YYYYMM'))

As per my understanding problem is with conversion date returned from @promt to character.

Thanks in advance

Amol

[Former Member]

So you're asking for a date in order to match with a yyyymm format integer?

Try something along the lines of:

TRUNC(TO_DATE(date*100+1,'YYYYMMDD'),'MM') = trunc(@Prompt('1_Select From Date http://ex.MM/DD/YYYY','D',,mono,free,not_persistent),'MM')

[Former Member]

try leaving the right hand side as a string and convert the left hand side to string as well.

something like this:

To_char(Date) = To_Char(@Prompt('1_Select From Date http://ex.MM/DD/YYYY','D',,mono,free,not_persistent),'YYYYMM')

[Former Member]

Thank you for the reply.

I have tried all possible of conversion but it is not working. I even tired truncating the @prompt to avoid time stamp problem. As per my tiral and error. To_Char(@prompt) is the area of problem. If i hard cord the value or use sysdate it is working fine.

regards,

Amol