I am using DB2 database,In Designer i am using @prompt function but giving errors in parsing.my database records will be like below
Join Date emp no emp name
====== ===== =======
07/08/04 100 ABC
07/10/04 200 DEF
so here i should display those two records in BO report.
I wrote this code in BO designer
JoinDate BETWEEN @Prompt(‘Enter From Date’,‘D’,mono,)) AND (@Prompt(‘Enter To Date’,‘D’,mono,)
it gave this error: The syntax of string representation of a datetime value is incorrect
JoinDate BETWEEN to_date(@Prompt('Enter From Date', 'A',, mono,), 'yyyy/mm/dd') AND to_date(@Prompt('Enter To Date', 'A',,mono,), 'yyyy/mm/dd')
Of course we suppose that you enter the date as a string in the format yyyy/mm/dd, which is alphabetically and cronologically ordered at the same time.
Please do not TYPE IN ALL CAPS as it is considered shouting.
Please do not demand an answer.
[quote:505625cdaf=“ForumTopics.com Terms of Service”]Please also remember that the moderators and helpful folks that you have and will meet here are volunteers. So please be respectful and mindful when awaiting responses. If you don’t get an answer, it may be that the right person hasn’t read your question yet, you placed it in the wrong topic area, or - strange as though it may seem - maybe nobody knows the answer.
[/quote]
Having said that, what is the exact datatype for your value? DB2 has both date and date/time fields. If it is a date/time field, you must provide both the date and the time for your prompt values.
Then talk to your DBA, find out the required format, find out the format being sent by the BusinessObjects query, and find out where they are different. Somewhere you are missing a part of the required format.
Duly noted. But I asked about the date format that the user was entering, along with the data format that the database is expecting. It has nothing to do with your prompt stucture, and everything to do with your data entry.
There is… if you want to know what the exact datatype is. Designer will show you date even if it is a DB2 date/time field. As I have mentioned, if it is a date/time field then you must enter a complete date/time value. Did you try that?
Please respect the services of Moderators who manage to answer our posts. They are not paid for it and they all are very senior level people and we all should be very thankful of them that they take time out of their busy schedules for answering our posts.
Dave is absolutley right as always…if it is a Date/Time value, you need to enter it in the format which is defined in your database sbo or prm files. You should first make sure what is the exact datatype of the object.
In addition to what dave posted (and I’ve stated it before): DB2 on AS400/I-series often use plain numeric 8-digit fields for their date-columns so knowing the exact datatype is of enormous importance… When you’ve got an AS400 emulator (if it is DB2 on AS400/I-series and not DB2 UDB on NT or something) you could easily check for the datatype by WRKQRY (as you’re not very eager to contact your DBA).