Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: RP on 19 Oct 2007 01:51:09 PM

Poll
Question: How to convert number format to date format
Option 1: Cognos 8 votes: 0
Option 2: Report Studio votes: 0
Title: Report Studio 8
Post by: RP on 19 Oct 2007 01:51:09 PM
Hi,

I have a dataitem called month and it is number format in Oracle database.
Now I want to this number item(200708) to date format i2 2007-SEP or SEP-2007.
I have given the follwoing expression and format changed to date,  even though it is not working.
Can any one help me on this
to_char(to_date([Month],'RRRRMM'),'RRRR-MON')
Thanks in Advance
RP
Title: Re: Report Studio 8
Post by: COGNOiSe administrator on 21 Oct 2007 08:56:03 AM
I might be wrong, but shouldn't it be:

to_char(to_date([Locality KPI Results].[Locality KPI Fact].[Month Key],'YYYYMM'),YYYY-MON')

Alternatively, if it doesn't work, try:

to_char(to_date(to_char([Locality KPI Results].[Locality KPI Fact].[Month Key]*100+1),'YYYYMMDD'),YYYY-MON')
Title: Re: Report Studio 8
Post by: RP on 22 Oct 2007 03:18:30 PM
Thank you, the code is woking fine, but I need how to do the ascending order, by default the oracle database takes as o1, now if we can addtogether it doesn't showind dascending order. could tell me ho to do the ascending also.
Thank you very much
Title: Re: Report Studio 8
Post by: COGNOiSe administrator on 22 Oct 2007 07:08:31 PM
Just sort it descending in the query itself. That should be easy.
Title: Re: Report Studio 8
Post by: RP on 23 Oct 2007 08:09:47 AM
Thank you it is working
Text only | Text with Images