How to convert date from 1,997 to 1997 and Month from 6 to June

[newyork66]

Hey, Can anyone tell me how to convert the date to a format I want

Now the report shows the Year as 1,997 . I want it to be 1997
and the Month as 6. I want it to be June.

Thank you!

[MFGF]

Hi,

Are you reporting of year and month as separate numeric items, or are they both part of a compete date item?

Assuming it's the former, you can use the data format option to define your year to display without the comma, and you can code a query calculation using either a case statement or an if-then-else construct to convert your numeric month values to month names.

Regards,

MF.

[newyork66]

THanks for your reply. I right clicked my YEAR filed, and clicked Data Format, The format Type is DATE. Do you know where I can change it to get rid of comma?

[MFGF]

Hi,

Within the list of date format properties, try setting the Pattern (the last property in the list) to yyyy

Does this fix your problem?

Regards,

MF.

[chow]

hi
1st i need to know how data look like in year Data item.and 1 more thing is the year and month are 2 different data items or one data item..

if year data item is separate data item do like this just
1st opent query studio and drage that year data item in your page then see how the data look like if it's 1,997 .then go to report studion and change the Aggregation function to none; rollup Aggregation function to Automatic.

if u want to change this permanentaly go to the FM and change the Attribute .

it will works

[cognos4u2]

Hi,

Go to the properties of the date data item, click on data format. Select format type as date and then make appropriate selections at Display Month, display years etc to convert to above format.
Thanks,
Nath.

[chow]

for no to month

use this to_char() function (ex: to_char(6,'mm') {{the syntax = to_char(exp[,string_exp])}} 

for year...do as i told before

[almeids]

chow-
   I'm curious, what value would the expression to_char(6,'mm') return?  In what database environment?
Steve

[COGNOiSe administrator]

I think for month (in ORACLE) it should be: to_char( to_date( to_char( 20000001 + month_number * 100 ), 'YYYYMMDD' ), 'MON')

Cheers

[GaryB]

In Informix same function to covert date.

[johnpenna]

With regards to the year format I'm guessing it returns an integer.I had this problem....

Could you try and format this value in the report with no thousands
separator.

Does that change the way it is displayed?

I changed the format to a number and then selected no thousands
separator...and it worked 2,007 now displays "2007"