extract year from date which is in number format

[Nandini.t]

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

[blom0344]

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

[barrysaab]

Thanks,Great Stuff,blom0344.

[Nandini.t]

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 .

[Gyana.Sahoo]

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

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

[Gyana.Sahoo]

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))

[HalfBloodPrince]

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

[Nandini.t]

Hi sekhar,

If i use that iam getting error.

[Nandini.t]

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

[blom0344]

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

[Nandini.t]

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