Project and Portfolio Management Practitioners Forum
cancel

Portlet Dates

Highlighted
Tim Brandish
Honored Contributor.

Portlet Dates

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.
12 REPLIES
Darshan Bavisi
Outstanding Contributor.

Re: Portlet Dates

Tim - Have you tried using the 'Date' validation? Not sure if it will work the way you want it to display.. but I believe there are two validations for this,

Date
Date (Short Format)

Other one is for choosing the date format,

Date Format
Scott Hawley
Regular Contributor.

Re: Portlet Dates

If you can't get it into the date format, just use the Oracle string functions to get it into 'YYYYMMDD' (adding HHMMSS if you want). It is a string but the alphabetical sorting is also chronological.
Lizabeth
Super Contributor.

Re: Portlet Dates

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.

Thanks for your help.
Darshan Bavisi
Outstanding Contributor.

Re: Portlet Dates

Hi Lizabeth,

Try using the trunc function with the to_date. Something similar to,

trunc(to_date(a.visible_parameter5,'yyyy/mm/dd hh24:mi:ss'))

or

trunc(to_date(a.visible_parameter5,'yyyy/mm/dd')) in case hh24:mi:ss are optional in your scenerio.

You may have to trick around a little bit with this or try using the to_char if you can for this also.
Lizabeth
Super Contributor.

Re: Portlet Dates

Thanks Darshan,

I have infact used this

and trunc(to_date(to_char(to_date(a.visible_parameter5,'yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss')) = trunc(to_date(to_char(to_date('[VP.USG_OFF_DATE]','yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss'),'yyyy/mm/dd hh24:mi:ss'))

But still no luck.
Erik Cole
Acclaimed Contributor.

Re: Portlet Dates

Lizabeth,

Have you tried using Date (short format) for your filter field validation?
Lizabeth
Super Contributor.

Re: Portlet Dates

Yes I have,

But strangely even though individually both Left and right hand sides do have the same results. I always have the oracle error 'ORA-01841: (full) year must be between -4713 and +9999, and not be 0
'.

Thanks
Erik Cole
Acclaimed Contributor.

Re: Portlet Dates

Lizabeth,

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'


I would probably do #2.
Lizabeth
Super Contributor.

Re: Portlet Dates

Thanks Eric for all your suggestions.

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.

What Am I still missing.

Thanks again.
Erik Cole
Acclaimed Contributor.

Re: Portlet Dates

Looks like you still have:

to_date(a.visible_parameter5,'yyyy/mm/dd hh24:mi:ss') OFF_RELEASE_DATE

in your inline view. Just put:

a.visible_parameter5 OFF_RELEASE_DATE

in there, and change the temp.OFF_RELEASE_DATE to:

to_date(temp.OFF_RELEASE_DATE,'yyyy/mm/dd hh24:mi:ss') OFF_RELEASE_DATE

That way you should only be "to_date()-ing" your inline view's results column which is text, but contains only dates and won't error out the filter.
Erik Cole
Acclaimed Contributor.

Re: Portlet Dates

Actually, don't do

to_date(temp.OFF_RELEASE_DATE,'yyyy/mm/dd hh24:mi:ss')

because your filter is doing it and you want that column to be text for the portlet anyway.
Lizabeth
Super Contributor.

Re: Portlet Dates

Thanks a ton for your help Eric, that finally worked. I had to change it to a view and remove all to_date functions from the select statements.

Thanks again.