DB2 Convert from YYYYMMDD to Date

Question

I have a column that stores a date as char in the format 'YYYYMMDD'. Now I want to convert it to a real date. I tried

select cast (DATEFIELD as DATE) as MyDate

But it only returns the old YYYYMMDD format labeled as 'DATE'. What am I doing wrong?

Solution

SELECT TIMESTAMP_FORMAT("DATEFIELD",'YYYYMMDD') as "MyDate"

Comments

[Chry007]

I tried your second suggestion but I get: NO AUTHORIZED CONVERT NAMED HAVING COMPATIBLE ARGUMENTS WAS FOUND SQL Code: -440, SQL State: 42884

[Asha]

this answer will alter the data table where the question is about how to get the data column converted.

[Hogan]

This is sql server not db2

[Asha]

@Hogan: The answer has been edited since my comment, Please take a look at edits. My commnet was relevant when there was an Alter statement.

[Gnqz]

@Hogan The question was also different, I really changed my answer since it was not relevant to the question anymore. Sorry for misguiding you.

[Hogan]

@Asha -- ugh sorry about that, I didn't look at the history.

[Paul]

On the iSeries, I'd sometimes have problems with TIMESTAMP_FORMAT and instead use TO_DATE