Cognos Date Prompt Macro Error

This issues applies to Cognos Report Studio Versions 8.3 through 10.1.1.

In Report Studio 8.3, I've got this raw SQL query running against a MySQL 5.1 data source:

SELECT enc.encounterID, enc.date
FROM enc
WHERE enc.date between #prompt('textPromptStartDate')# AND #prompt('textPromptEndDate', 'date')#

This produces text prompts for the user. If the user enters dates into these prompts in the format of 'YYYY-MM-DD', e.g. '2010-01-15', the query works fine. But I want to replace the text prompts with proper Date prompts. When I try replacing the above prompts with

#prompt('datePromptStartDate', 'date') AND #prompt('datePromptEndDate', 'date')#

the query runs (no errors are generated), but I get an empty result set. I have a feeling I need to adjust the date format that the date prompt macro returns, but I'm stuck after many hou开发者_StackOverflowrs of experimentation as to how to debug this.

---

For dates, you need the single quotes around the value. I do this using the "sq" function:

#sq(prompt('DateParameterFromPromptPage', 'date'))#

---

You can create a prompt page with date prompt controls, and then bind them to the parameters in your prompt macros. I tested this out in 8.4 with MySQL 5.1 (ODBC) using the query you have above and it worked. I also had the same problem you experienced with no data being returned prior to creating a separate prompt page and binding the controls.

The steps you would take to do this are:

1. add a prompt page (if one doesn't exist) to your report
2. add 2 date prompt controls to the prompt page
3. in the "Parameter" property for the first date prompt, use: datePromptStartDate
4. in the "Parameter" property for the second date prompt, use: datePromptEndDate

When you execute the report, the new prompt page will replace the prompt page that is generated from the macro, and the parameters in the macros will be bound to the date controls.

Edit: I forgot to add that the macros in your SQL should look like the following:

SELECT enc.encounterID, enc.date 
FROM enc 
WHERE enc.date between #prompt('textPromptStartDate')# 
AND #prompt('textPromptEndDate')#

---

It is possible to create a prompt without a prompt page for the date prompts without using a prompt page. I ran into this exact problem as well on SQL Server until I ran a profile trace. The profile trace showed that Cognos wasn't passing quotes around the date value. I fixed the issue by passing the prompt as a string, replacing the default validation test string (testValue) with a real date, building the string in a variable, and then passing it to the actual query. Here's what I used to convert a date prompt into a normal DATE data type.

DECLARE @StartDate DATE = CONVERT(DATE, REPLACE(#prompt('StartDate')#, 'testValue', '1/1/2012'));