Date format exception with @prompt() function

building reports desktopintelligence
1

Appln: DesktopIntelligence
Version: BOXI-R2@SP2
DataProvider: Oracle 9i
OS : Windows 2000

When I use a date variable created by @prompt() function, it only accepts mm/dd/yyyy format.
With any other format the error that I get is:

Exception: DBD, ORA-01847: day of month must be between 1 and last day of month
State: N/A

My previously created reports were refreshed and saved with variables having dd-mmm-yyyy format.
The same reports are now not accepting dd-mmm-yyyy format.
The issue is same for both universe based and Free-hand-Sql based reports.

Some more investigation into it left me bewildered
:shock:
@variable() function accepts dd-mmm-yyyy format.
Also, sql query runs in BO if I hard code a date in dd-mmm-yyyy.
e.g all the below queries run fine

select count(1) from table_xyz where column_date=@variable('Enter date') [ user input = 31-Jan-2008 or 31/01/2008 ]-works
select count(1) from table_xyz where column_date='31-Jan-2008'	-works
select count(1) from table_xyz where column_date='31/01/2008'	-works

BUT

select count(1) from table_xyz where column_date=@prompt('Enter date','D',,mono,free) [ user input = 01/31/2008 ]-works
any other format doesn't work. This is what I'm concerned about.

However,

select count(1) from table_xyz where column_date=@prompt('Enter date',D,,mono,free) [ user input = 31-Jan-2008 or 31/01/2008 ]

WORKS ??? :blue: HOW :crazy_face:
just by removing single quotes around D in @prompt() function, it works for dd-mmm-yyyy format which is what I required (I don’t know should I be happy or should I cry)
but doesn’t work for mm/dd/yyyy format which it previously supported with D having single quotes around it …(who cares anyways ) :expressionless:

I could have removed quotes around ‘D’ in all @prompts() variables in all universes but my collegues have no problem with same report.
i.e. For them, dd-mmm-yyyy is working for all functions and single-quotes around ‘D’ doesn’t make any difference.
:evil:
So I thought better not to touch any universe and search for actual root cause. :hb:

I suspected my PC for regional settings.
I changed my Regional setting’s date format to dd-mmm-yyyy (both short and long) still no relief. :flush:

I know all this stuff sounds really confusing and wierd but it’s true.

Can somebody save my day please…

Regards,
JK

JK-BO :india: (BOB member since 2008-03-20)

2

Hi,

In @Prompt function if u wish to enter dates in dd/mm/yyyy or dd/mmm/yyyy, then in @Prompt syntax use ‘A’ in place of ‘D’ like

@Prompt(‘Enter Date’,‘A’,‘Class_Name\Object_Name’,mono,free)

Try it if it works in your case and give your feedback.

Regards

soni_ak :india: (BOB member since 2008-09-15)

3

https://bobj-board.org/t/84923

Have a look at the post by Reporter Bloke in the post above, it may help you…

Mak 1 :uk: (BOB member since 2005-01-06)

4

Hi soni_ak,
Yes, changing from ‘D’ to ‘A’ works…
But you would not want to change each and every object/conditions having @prompt function, in all universe.
Moreover, this is the first time I’m encountering such errors and only on my computer.
The universes and reports we are using are 2-3 years old and never had such problems.
So, re-installing BO client is the only option I can think of. But before that I wanted to get the root cause of it.

Hello Mak,
The link you gave made me happy… as in I am not the only person on earth having this kind of problems. And a good checklist for future refrence.
But sadly, the given solution didn’t work for me.
:cry:

I’m really struggling to get rid of it…

JK-BO :india: (BOB member since 2008-03-20)

5

Hello,

I am experiencing the same issue, do you already have some additional experience about this issue?

thank you a lot

regards

emzet

emzet (BOB member since 2010-06-17)