How to make date field show year month only

[gatorfe]

I have a date field that I want to change to only show the year and month.  The date currently shows as 2011-07-20.  I want it to show 2011-07.  I also want to be able to group this new calculation so all the lines in the report for 2011-07 are together. How can this be done?  thanks in advance for your suggestions!

[Arsenal]

I think extract(monthyear, date_queryitem) might help you out to extract the just the year and month and then just group on the list column that is being fed by this newly extracted data item

[ksr]

Just Rt click on the field,
select style,
select "Data format"
select Format Type as Date
Select YES for the "Display Years" property
Select YES for the "Display Months" Proerty

[gatorfe]

neither worked :(

[Lynn]

If you want to group on it then I don't think you can use the data format properties since those will just change how the value is displayed.

Extract returns integer values so you'd need to convert them back to strings to paste them together in the format you want (then you'd also need to do something for preceding zero for months with only one digit).

If you are using Oracle the TO_CHAR function is ideal for this sort of thing. Or you can try the cast function to convert to a string and then substring it:

Code Select Expand

substring( cast ( [Namespace].[QuerySubject].[Date Field], VARCHAR(10) ), 1, 7 )

[Mpotla]

Hi ,

we can set this using Pattern property in the data format.

Select the data item->Data formar(in the properties)->Select type as Date-> down in the properties list  we have something called Pattern. Just put your pattern YYYY-MM. Set Yes to Display Years, Yes to Display months.

Hope this would help!