Print Page - How to convert YYYYMM to YYYY/MM

Title: How to convert YYYYMM to YYYY/MM
Post by: kane on 15 Dec 2010 07:51:19 AM

Hi All,

Any one can help me how to convert YYYYMM to YYYY/MM ....please :(

Title: Re: How to convert YYYYMM to YYYY/MM
Post by: Lynn on 15 Dec 2010 08:09:41 AM

The answer can vary depending on the data type and DBMS are you using.

If it is a number you could try:

substring(cast([YourField],CHAR(6)),1,4) || '/' || substring(cast([YourField],CHAR(6)),5,2)

Personally, I think the ideal situation is if you can get this done on the database side as part of the ETL process (if that applies in your situation) or in a view definition.

Title: Re: How to convert YYYYMM to YYYY/MM
Post by: macpao64 on 15 Dec 2010 08:17:54 AM

hi,

you can create a query calculatuon or a layout calculation,
and write this string: to_char([name_of_field], 'yyyy/mm')
into Expression Definition

bye

Title: Re: How to convert YYYYMM to YYYY/MM
Post by: kane on 15 Dec 2010 08:30:58 AM

Hi Macpao64,

hi, my Column name is Monthid = 201004...i need to convert to 2010/04 ...
I tried by using sting iam getting error. Iam using oracle database.

RQP-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-28'.
UDA-SQL-0114 The cursor supplied to the operation "sqlOpenResult" is inactive.
UDA-SQL-0107 A general exception has occurred during the operation "open result".
ORA-01481: invalid number format model

Title: Re: How to convert YYYYMM to YYYY/MM
Post by: MFGF on 15 Dec 2010 10:14:02 AM

Did you try Lynn's suggestion?

Title: Re: How to convert YYYYMM to YYYY/MM
Post by: sir_jeroen on 29 Dec 2010 04:00:29 AM

what about: to_char(to_date([field];'yyyymm');'yyyy/mm')

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Framework Manager => Topic started by: kane on 15 Dec 2010 07:51:19 AM