Getting Monthname from a date field

[aji136]

Hi,

How do I get monthname from a datefield - I have tried the function monthname(fieldname) but it comes up with an error. I am using Report Studio v8.2. The source database is Access. Appreciate if anyone can help me on this...

Error Message:
RQP-DEF-0177

       An error occurred while performing operation 'sqlPrepareWithOptions' status='-69'.


Cheers,
Aji

[Suraj]

First of all, create a data item that extracts month using extract (month, [Date Field]). This will give you 1, 2 3, ... depending on Jan, Feb, Mar, ...
Then, create a case statement such as
case
when [data item] = 1 then 'January'
when [data item] = 2 then 'February'
.
.
.
when [data item] = 11 then 'November'
else 'December'
end

This will replace the extracted month to month names.

[aji136]

Thanks Suraj but this is what I had done as a workaround. I thought there would be an easy way out by just specifying monthname(date field). This works fine directly from MS Access query. Though Cognos includes this function as part of the Access function list, it doesn't seem to work. Have you got this function to work directly from Cognos?

[Suraj]

We have month indicator in our date field that we can compare numbers with to get month name. So we don't have to write that case statement.

[Modtamod]

to get month type in this expression
extract(month,date expression)

[MFGF]

If you just want to show the month name from your date field, use the Format property of the date value and you can take control of which parts of the date are displayed and how they are displayed.

Regards,

MF.