DB2 Date function to convert number to Date
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.
Software/Hardware used:
Software/Hardware used:
ASKED:
April 5, 2005 11:43 AM
UPDATED:
April 19, 2013 6:03 PM
Answer Wiki:
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.
To see all answers submitted to the Answer Wiki: View Answer History.
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’.