Date prompt Issue!!

building reports webintelligence
1

I tried to run this prompt in infoview and I got and error:
A database error occured. The database error text is: ORA-01830: date format picture ends before converting entire input string
. (WIS 10901)
The code is validated successfully but when running the report only this error comes.

This is the code of the prompt:-

trunc(Table.Date_field) in (Case when to_date(@Prompt('Date','D',,mono,free),'dd/mm/yyyy') = '01/01/1900' then to_date(sysdate,'dd/mm/yyyy') else to_date(@Prompt('Date','D',,mono,free),'dd/mm/yyyy') end )

I tried trunc so as to strip the time stamp part. My database is Oracle.

Pls Help…

akl007 (BOB member since 2010-03-23)

2

Sysdate also includes timestamp data. Replace
to_date(sysdate,‘dd/mm/yyyy’) with
trunc(sysdate) and try that

Chas :uk: (BOB member since 2004-10-26)

3

Tried that… still getting the same error while running the report! :frowning:

akl007 (BOB member since 2010-03-23)

4

Can I ask why you are trying to turn sysdate into a date? It’s already a date and needs no conversion.

You may want to present the format in the prompt so the end users know what to enter, or you might be able to use a format mask in the to_date conversion to handle some of the single number values (like day or month):

to_date( @Prompt(...)' 'FMFXmm/dd/yyyy')

digpen :us: (BOB member since 2002-08-15)

5

I don’t want the time stamp part of the sysdate. Thts why I tried to convert using to_date.

akl007 (BOB member since 2010-03-23)

6

to_date should be used only when you try to convert a string into Date type. You’ve to use to_char() to get the desired format.

Jansi :india: (BOB member since 2008-05-12)

7

As u told I have changed to_date to to_char as:

 trunc(Table.Date_field) in (Case when to_char(@Prompt('Date','D',,mono,free),'dd/mm/yyyy') = '01/01/1900' then to_char(sysdate,'dd/mm/yyyy') else to_char(@Prompt('Date','D',,mono,free),'dd/mm/yyyy') end )

But now I m getting this error while running the report.

A database error occured. The database error text is: ORA-01722: invalid number
. (WIS 10901)

akl007 (BOB member since 2010-03-23)

8

I have just tried the following and it works as expected. Amend for your needs and try it:
trunc(Date Field) = (case when to_date(@prompt(‘Date’,‘D’,mono,free)) = ‘01/01/1900’ then trunc(sysdate) else to_date(@prompt(‘Date’,‘D’,mono,free)) end)

Chas :uk: (BOB member since 2004-10-26)

9

Tnx a lot dude… That works!!

akl007 (BOB member since 2010-03-23)

10

I was talking only about the green part and not the red part, because @prompt returns a string and so to_date() should be used. Anyways, it is good it is now resolved. :slight_smile:

Please do not use Instant Message abbreviations on BOB. We want to keep BOB easy to understand for our International community. Thanks.

Jansi :india: (BOB member since 2008-05-12)