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
VinezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

VinezAuthor Commented:
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
VinezAuthor Commented:
Never mind I will try to close this case.  It seems that you do not have that many Cognos 8 Report Studio experts.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
megansbayCommented:
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
VinezAuthor Commented:
I did find the answer but that is a great tip.  Thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Reporting Tools

From novice to tech pro — start learning today.