DB2 Date function to convert number to Date

pts.
Tags:
Development
IBM DB2
In Cognos-Framework Manager there is a calculation in the DB2 section called "Date". Supposedly this field will change a numeric date "01012005" to an actual date "01/01/2005". But, we tried it and it does not work. Can you see if you can find anything that will allow us to create a calculated field in framework manager that will convert the dates for us.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Hi meenac, I haven’t dealt with DB2 for 14 years but this is the way it was, and may help. The DB2 DATE function is intended to work with the DATE data type. The ISO format is the default that come with DB2, but the system installer can change these defaults. ISO format is YYYY-MM-DD e.g 2005-12-31. Assuming your system has not overriden the ISO, if you were able to move the field into a DATE field then you could use the CHAR function to specify the EUR or USA format. CHAR returns the date as a character string including delimiters e.g CHAR(your column name, EUR) = 31.12.2005 or CHAR(your column name, USA) = 12/31/2005. Hope that helps.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • TheQuigs
    I know this is quite an old post, but here's some information that might help. You could try using the following SQL functions DATE(CHAR(numeric-value)). I've never tried to convert a number in the format mmddyyyy to a true date. Also, here's a link on 'String Representations of Datetime Values' in the 'DB2 Universal Database for iSeries SQL Reference'.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following