Passing Date Parameter to Oracle Query

ActivityDate is a report parameter set up as a date that I'm trying to pass into an Oracle query. The specific WHERE clause is

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

When I run the query from the Data tab, all works as expected. I suspect the reason is that I under the date as 2005-12-31. If I enter the date as 12/31/05, the query fails ("Not a valid month") unless I change to function's format to 'MM/DD/YYYY' in which case I, again, get good results.

But when I run the report from Preview, I get no results at all no matter what format I use in the function.

Any chance any of you have seen this and know how to work with it?

The question is what is the data type of the report parameter specified as? Try it as a string instead of a date.|||If I type it as string, then the user has to key in the date rather than use the calendar pop-up. I don't want to force that.|||

Could someone please take a look at this again. Although this question is old, I'm still stuck for an answer.

I think what I need to know is this: If I set a report parameter's data type to DateTime, what is the format of the returned value? As mentioned in the first entry of this thread, what I need to do is:

WHERE PROJ_DATE = TO_DATE(:ActivityDate,'YYYY-MM-DD')

'YYYY-MM-DD' (and many other formats I've tried) doesn't work. What's right?

|||Hi,

Did you ever find a solution to this problem?

Cheers

Phil|||No, I'm afraid I never did. I've been running the report manually until I can take the time to revisit the problem.|||

Use this syntax: WHERE PROJ_DATE = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

|||

Use this syntax to omit time portion:

WHERE TO_DATE(PROJ_DATE), 'DD-MON-YY') = TO_DATE(TO_CHAR(:ActivityDate), 'DD-MON-YY')

sql