char to date in DB2

[SarahK]

I have a character field mmddyyyy. I need to convert this column to a date because I would like to do some date manipulation with it. When I use DATE(table.column), all of the data disappears! Could anyone help me with this?
I am connected to a DB2 database, BO 5.1.6

Thanks in advance!!
-Sarah

 

[burkek]

Are you attempting the conversion at the Universe or Report level? If at all possible, I would attempt it at the Universe level!

 

[SarahK]

I am attempting this at the universe level. Any suggestions??? THanks in advance,
Sarah

 

[blom0344]

Hello Sarah,

Char or Varchar that is a valid representation of a date (i.e. "2003-04-01&quot can be converted to a real date in DB2 by using the DATE function.
So , in your case this may mean reworking the string by using functions like CONCAT and SUBSTR:

SUBSTR(FIELD,5,4)||'-'||SUBSTR(FIELD,1,2)||'-'||SUBSTR(FIELD,3,2) T. Blom
Information analyst
tbl@shimano-eu.com

 

[SarahK]

That is the exact syntax I ended up using. It took me awhile because of a silly little thing I was leaving out... the dash! Thank you for your help!
Sarah