Cognos 8.3 - date prompt macro problem
- by Jacob
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 hours of experimentation as to how to debug this.
