?
Solved

Cognos 8.2 Report Studio, date-to-string

Posted on 2008-11-17
4
Medium Priority
?
9,838 Views
Last Modified: 2012-05-05
I'm trying to format two date fields in Cognos 8.2 Report Studio.  The two date fields are Project Start Date and Project End Date.  The result should display in this format,  mm/yy - mm/yy.    So far I have I've got the format, however I need the month to always be two characters, for example January would 01 instead of 1.  Please see my code below.  What I have now works but does not give me two characters for month.  Can someone help with this?

substring(cast(extract(month,[Project Start Date]),VARCHAR(10)),1,2) + '/' + substring(cast(extract(year,[Project Start Date]),VARCHAR(10)),3,2) + ' - ' + substring(cast(extract(month,[Project End Date]),VARCHAR(10)),1,2) + '/' + substring(cast(extract(year,[Project End Date]),VARCHAR(10)),3,2)

thanks,
vinez
0
Comment
Question by:Vinez
  • 3
4 Comments
 

Author Comment

by:Vinez
ID: 22978428
I'm trying to format two date fields into one calc field.  I'm using Cognos 8.2 Report Studio.  Cognos 8.2 does not have the date-to-string function, therefore the code I created is below.  The two date fields that I'm using are Project Start Date and Project End Date.  The desired result should display in this format,  mm/yy - mm/yy.   However, it displays this way, 1/08 - 3/08.  Basically I want the month to be two digits, example 01/08 - 03/08.  I need the zero to display for missing values.  Below is the code that needs to be modified or redone.

substring(cast(extract(month,[Project Start Date]),VARCHAR(10)),1,2) + '/' + substring(cast(extract(year,[Project Start Date]),VARCHAR(10)),3,2) + ' - ' + substring(cast(extract(month,[Project End Date]),VARCHAR(10)),1,2) + '/' + substring(cast(extract(year,[Project End Date]),VARCHAR(10)),3,2)
0
 

Accepted Solution

by:
Vinez earned 0 total points
ID: 22984341
Never mind I will try to close this case.  It seems that you do not have that many Cognos 8 Report Studio experts.
0
 

Expert Comment

by:megansbay
ID: 24044600
Vinez,

I hope that you found your answer. Just in case you did not, my question is why are you coding the format? In Report Studio, you can simply add a text item and change the source to Data Item Value. Select your date field and choose the data format property. In that dialog you can specify the format. If you want to insure that two positions are shown, just enter a format string in the "Pattern" option.

Cheers!
0
 

Author Comment

by:Vinez
ID: 24049706
I did find the answer but that is a great tip.  Thank you.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Loops Section Overview
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question