asked on

Cognos 8.2 Report Studio, date-to-string

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

Vinez

ASKER

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)

ASKER CERTIFIED SOLUTION

Vinez

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

megansbay

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!

Vinez

ASKER

I did find the answer but that is a great tip. Thank you.