Business Objects Prompt Filter



I have a task of creating a report where I have to get the list of employees who were born in a particular month based on the Month End Date Entered. I used both below prompt filters,

to_char(( HRMART.EMP_DIM.BIRTH_DT ),'MM') =to_char(to_date(@Prompt('Enter Month End Date:','D',,,),'MM/dd/yyyy'),'MM')

(or)

to_char(( HRMART.EMP_DIM.BIRTH_DT ),'MM') =to_char(to_date(@Prompt('Enter Month End Date:','A',,,),'MM/dd/yyyy'),'MM')


but I get this error "Database error: ORA-01843: not a valid month. Contact your Business Objects administrator or database supplier for more information. (Error: WIS 10901)"

Can someone suggest on how to do this?

Thanks in Advance
LVL 1
nani11Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
mlmccCommented:
Is the prompt working?

Try entering the date in the system format based on the regional settings.

mlmcc
0
 
nani11Author Commented:
What is 'system format based' date?
0
 
mlmccCommented:
The regional settings under control panel

mlmcc
0
 
James0628Commented:
I wonder if it might be because one or more records don't have valid dates in that field (as opposed to a problem with the prompt).  You could try something like the following instead and see if you get the same error:

to_char(( HRMART.EMP_DIM.BIRTH_DT ),'MM') ="11"


 If so, then you've apparently got one or more records where BIRTH_DT is not a valid date and you'll need to figure out what those invalid values are and how you want to handle them.

 James
0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
mlmccCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.