i need to select records in between dates

hi

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


bo_s_user (BOB member since 2004-05-19)

Is that literally the syntax of the object? Seems there are some extraneous / unbalanced parentheses. Try this:

JoinDate BETWEEN @Prompt('Enter From Date','D',,mono,) AND @Prompt('Enter To Date','D',,mono,)

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

hi Dwayne Hoffpauir

Unfortunately not working that statement. It is giving the same error,which i already wrote.


bo_s_user (BOB member since 2004-05-19)

hi

NEED HELP IMMEDIATLY

YOUR HELP IS APPRICIATED


bo_s_user (BOB member since 2004-05-19)

Try also this:


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.


Donald Duck :mexico: (BOB member since 2004-07-29)

Remember that it’s better to receive a string from @Prompt: this is the reason why I use ‘A’ instead of ‘D’ …


Donald Duck :mexico: (BOB member since 2004-07-29)

hi Donald Duck

Still displaying eoor like the data type, length or value of argument “2” of routine SYSIBM.TO_DATE is incorrect

please give another

thank u


bo_s_user (BOB member since 2004-05-19)

hi donald

i used A instead of D even though giving error

is there any way to do

:?:


bo_s_user (BOB member since 2004-05-19)

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]

Basic Netiquette

Thanks.

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.


Dave Rathbun :us: (BOB member since 2002-06-06)

Hi,

still i am getting error


bo_s_user (BOB member since 2004-05-19)

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.


Dave Rathbun :us: (BOB member since 2002-06-06)

hi,

this is the statement i am using in BO Designer,My enddatabase is DB2

DATE(JOIN_DATE) >= DATE(@Prompt(‘Enter From Date’,‘A’,)) AND DATE(@Prompt(‘Enter To Date’,‘A’,)) <= DATE(JOIN_DATE)

i am getting same error, that i have given already.

i think there is no need to contact DBA.


bo_s_user (BOB member since 2004-05-19)

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. :wink:

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?


Dave Rathbun :us: (BOB member since 2002-06-06)

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.

kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

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).


jobjoris :netherlands: (BOB member since 2004-06-30)