macro for date between

[pooja]

we need help to write marco for date range, please
we have datetime as -- [active_date]
how to write date between macro to acheice like FROm_date & TO_date on prompt page
(note- we are pulling data by writing a direct SQL)

[CognosAdmn]

Hi Pooja,

Please create the prompt page with two Date Prompts and two parameters P_FromDate and P_ToDate.

and insert this into your where clause in the direct SQL part.

- where Date between #prompt('P_FromDate')# and #prompt('P_ToDate')#)


this worked for me for a similar request. Maybe others may have better suggestions.

Shaam.

[pooja]

Hi Admin

I twrote this way in my sql after where clause--but I got a ERROR

B.active_date between(#prompt('P_FromDate')# and #prompt('P_ToDate')#)

please advice

[CognosAdmn]

please try this:

B.active_date between #prompt('P_FromDate')# and #prompt('P_ToDate')#
---removed the outer brackets....I apologize, i just noticed I have an outer closing bracket my mistake.

what is the error message you got?

Thanks,

Shaam.

[pooja]

I tried but still ERROR--pardon me If i miss somethig, please

UDA-SQL-0144 An arithmetic exception was detected. [IBM][CLI Driver][DB2] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007 RSV-SRV-0042 Trace back: RSReportService.cpp(680): UDASQLException: CCL_CAUGHT: RSReportService::process() RSReportService.cpp(625): UDASQLException: RSAsyncThrowable: RSReportService::process RSASyncExecutionThread.cpp(203): UDASQLException: CCL_CAUGHT: RSASyncExecutionThread::run(): asynchRunSpecification_Request RSASyncExecutionThread.cpp(669): UDASQLException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_Request RSQueryMgr.cpp(1676): UDASQLException: CCL_RETHROW: RSQueryMgr::executeRsapiCommand QFSSession.cpp(1435): UDASQLException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSSession.cpp(1433): UDASQLException: CCL_CAUGHT: QFSSession::ProcessDoRequest() QFSSession.cpp(1401): UDASQLException: CCL_RETHROW: QFSSession::ProcessDoRequest() QFSConnection.cpp(1540): UDASQLException: CCL_RETHROW: QFSConnection::DescribeDataSourceQuery QFSQuery.cpp(439): UDASQLException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery QFSQuery.cpp(439): UDASQLException: CCL_RETHROW: QFSQuery::DescribeDataSourceQuery Source/AQE_UDAFacade.cpp(1685): UDASQLException: CCL_RETHROW: apidyn.cpp(240): UDASQLException: CCL_RETHROW: sqlPrepareWithOptions

[CognosAdmn]

what's your date datatype? sounds like your date values are stored as string or varchar is that correct? if it is you may have to cast or convert your date in the WHERE clause.

what type of prompt did you select in the prompt page? date prompt? Date&Time prompt? or value prompt?

could you try inserting real date values in the where clause and test it without the prompt. Please let me know if that works.

Also, since you are using directSQL, what database are you running?

[pooja]

hi Admin,
some details

Dbase isd -- DB2
DType-char

I have total of 6 prompt macro and all are working fine except this [Active_date]......

please advice

[CognosAdmn]

ohh oops.  :)

Sorry, I am not familiar with DB2. :-\

I know it takes the parameters differently, the code i provided was for MS SQL Server. You could try leaving the single quotes out: (below)
------   B.active_date between #prompt(P_FromDate)# and #prompt(P_ToDate)#
You may need to replace the single quotes with something else like double quotes or ||.

The other 5 prompt macros that work fine, do they have the same data type in DB2? 

Have a Great Lunch.  :)

[pooja]

Others are with char, integer and so on but works fine...and of course DB2 as well

I wonder someone helpme out

thankyou Admin so so much for trying

[CognosAdmn]

You are welcome.

I am sorry I am not of any big help. Try googling this part of the error message:SQL0180N The syntax of the string representation of a datetime value is incorrect. it is related to your DB2 date field. Good Luck.  :)

[pooja]

It Ok Admin,

I am still trying, lets C...

But again, kindly thank you so much

[david.stachon]

why not use in the date range functionality that exists right out the box?

[active_date] in_range ?p_active_date?

...this will generate a proper date range prompt with one parameter.

[pooja]

we are writing direct sql, so we need to write macro for prompt

thank you for any help

[paddhu]

Pooja, if you can get to see the database then see how the date is stored in your field. I think the problem is only that you are entering the date in a different format. As you have said, your field is Char type; so in your sql query, first convert it into date format (use any date casting functions from the DB2 function set) and the same to your prompts. Hope this helps.