8.4 RS - Date Format not kicking in!!

[cognooby]

Hi

I have simple x-tab report in report studio that counts the number of customers per month against some attributes like Region, Location, etc. 
The only column is the DATE column which should contain the total customers flagged active during that particular month. 

The backend fact table is in Oracle and there is a row for each customer in each month.  Fields include customer_id, month, status etc.  The "month" field is stored as VARCHAR2 and contains a description of the month - eg "January 2009"

When I pull these into a report, I expect to see the name of the month across the top row e.g.
( I used "|" here to show you the different columns of the report)

|January 2009|February 2009|March 2009|.....

with the value for the # of customers in the rows underneath. 

It is important for Cognos to recognize the field as a "date" because I'd like to sort them in ascending order. 

I understand that since the field is stored as char, Cognos can't treat the values as true dates.  To overcome this, I defined the MONTH column using the following expression:
to_date([MONTH_field],'MONTH YYYY').

Now, Cognos is able to sort the columns properly, but its displaying a completely different format than what I want!! I want "January 2009" but what I am getting is:

2009-01-01 00:00:00.000

I also know that I can use RS to over-ride the data pattern globally (i.e. general data formats) or locally (each xtab member field).  I tried both approaches, changing to the following:

Format type: DATE
Pattern: "MMMM-yyyy"

But I still can't get the value to display properly.  The report runs ok, I dont get any errors, but the field titles still show up as:

2009-01-01 00:00:00.000

How can I fix this?

Thanks

[Lynn]

Is your pattern entered with the double quotes? I don't think you should have those.

If that isn't the issue then I'm not sure as I don't spot anything wrong with the pattern, although you could also try to use the individual properties to get the format you want.

You might also try using the column that you have with the text the way you like it and then use your derived column created with the to_date just as a sort key (in advanced grouping and sorting)

[cognooby]

Thanks for getting back so fast.  Well, I tried it with and without the quotes - same result!

I finally tried putting the entire to_date() string inside a cast to convert it into date - e.g.
cast(todate(), date) and now the column headings are showing up as "2009-01-01" so its a bit of an improvement!! Still, playing around with the display mask settings have no effect on it....

any other ideas?

[Lynn]

Did you try getting rid of the mask and using individual properties?

[cognooby]

Yes, i did try changing the global and local pattern settings.  No luck.  I'm stuck with "2009-01-01" :(

[Arsenal]

I wonder if something like to_char(yourtimestamphere, 'MMMM yy') might work

yourtimestamphere = the original value that Cognos is returning without any to_date etc. castings i.e. 2009-01-01 00:00:00.000