Does anyone know how to define a column in a datasource as a date and get it to display in any format besides mm/dd/yy when it shows up in the portlet? If I convert it to a string, I'll lose the ability to sort by date.
We are in a fix too, can you help us sort this out.I have tried all possible combinations that I could think off, but no luck.
The Filter is written as - and to_date(a.visible_parameter5,'yyyy/mm/dd hh24:mi:ss') = to_date('2008-04-17 00:00:00','yyyy/mm/dd hh24:mi:ss') where the value '2008-04-17 00:00:00' is the calander date picked up by the user( filter field), with the validation as Date.
You usually get this error because your a.visible_parameter5 column can contain things other than dates (nulls, text) depending on the request type that applies to any given row. When Oracle tries to apply your to_date() to filter out one of these non-date rows, it throws the error.
A couple of things you can try:
1- Try including something in the "where" clause (usually request type will do) that limits the query to only return rows where visible_parameter5 is a date. You might have already done this, but also this may or may not work depending on how Oracle rewrites the query (the order it applies your predicates) at run time.
2- If Oracle still chokes, write a static or an inline view that accomplishes #1 above and then query that view with your portlet/filter.
3- Compare strings instead of dates:
and to_char(VISIBLE_PARAMETER5) = '2007-05-11 00:00:00'
Attached herewith is the Query that I am using after the modifications that were suggested, Although I do not get the error as before, but for any value for the Date that I choose in the filter criteria, the query returns no results.