Print Page - extract year from date which is in number format

Title: extract year from date which is in number format
Post by: Nandini.t on 23 Dec 2011 02:49:46 AM

Hi all,

I have a data item as reporting period which is in number format( eg : 201112, 200911).
Can i get some help to extract year and month from the reportinng period.

Thanks in advance for any help....

Cheers,
Nandini

Title: Re: extract year from date which is in number format
Post by: blom0344 on 23 Dec 2011 02:55:55 AM

year: _round([reporting_period]/100)
month: cast(substring(cast([reporting_period];varchar(6)),5,2),integer)

Title: Re: extract year from date which is in number format
Post by: barrysaab on 23 Dec 2011 02:58:30 AM

Thanks,Great Stuff,blom0344.

Title: Re: extract year from date which is in number format
Post by: Nandini.t on 23 Dec 2011 03:16:04 AM

Thanks blom. :)

But while using round function am facing error as (UDA-SQL-0460 A general exception has occurred during local processing. UDA-EE-0020 The "Expression Engine" node "round" requires more operands.)
Pl can u guide me some other way.

I need to get the number format 'yyyymm' as 'mm/yyyy' format .

Title: Re: extract year from date which is in number format
Post by: Gyana.Sahoo on 23 Dec 2011 03:29:00 AM

YEAR : Substring([reporting_period],1,4)
Month : Substring([reporting_period],5,2)

Then concatenate (YEAR||'/'||MONTH)

Title: Re: extract year from date which is in number format
Post by: Gyana.Sahoo on 23 Dec 2011 03:30:37 AM

If you get any error, then Use "cast" function

Cast(Substring([reporting_period],1,4),varchar(4))
Cast(Substring([reporting_period],5,2),Varchar(2))

Title: Re: extract year from date which is in number format
Post by: HalfBloodPrince on 23 Dec 2011 04:07:29 AM

Try this
substring(cast([Reporting_period],varchar(6)),1,4)

Title: Re: extract year from date which is in number format
Post by: Nandini.t on 23 Dec 2011 04:11:21 AM

Hi sekhar,

If i use that iam getting error.

Title: Re: extract year from date which is in number format
Post by: Nandini.t on 23 Dec 2011 04:11:57 AM

Thanks Prince :) :)
It worked !!!!

Title: Re: extract year from date which is in number format
Post by: blom0344 on 23 Dec 2011 04:25:29 AM

year: _round(([reporting_period]/100),0)
month: cast(substring(cast([reporting_period],varchar(6)),5,2),integer)

Bit too hasty, above the corrected expressions. Check whether you need year/months as numerical values or strings.

The above are for numerical ones

Title: Re: extract year from date which is in number format
Post by: Nandini.t on 23 Dec 2011 06:01:00 AM

i tried this also and it works blom. Thanks...

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: Nandini.t on 23 Dec 2011 02:49:46 AM