[casting] Converting a string to a date in DB2

I am working with a DB2 database for the first time.

I am trying to work with DB2 dates, but the data is stored as a string in the DB2 database.

I want to convert this date-string into an actual date, preferably dropping off time because I want all transactions between 1 Jan 2011 and 26 Jan 2011.

So essentially, I want this MS SQL statement in DB2 magic...

CONVERT(datetime,SETTLEMENTDATE.VALUE,103)

For background, I have got as far as

CAST(SETTLEMENTDATE.VALUE, DATE)

AND

DATE(SETTLEMENTDATE.VALUE)

But I need the expert knowledge of a DB2 whizzkid!

Thanks

Okay, seems like a bit of a hack. I have got it to work using a substring, so that only the part of the string with the date (not the time) gets passed into the DATE function...

DATE(substr(SETTLEMENTDATE.VALUE,7,4)||'-'|| substr(SETTLEMENTDATE.VALUE,4,2)||'-'|| substr(SETTLEMENTDATE.VALUE,1,2))

I will still accept any answers that are better than this one!

I know its old post but still I want to contribute
Above will not work if you have data format like this
'YYYMMDD'

For example:

Dt
20151104

So I tried following in order to get the desired result.

select cast(Left('20151104', 4)||'-'||substring('20151104',5,2)||'-'||substring('20151104', 7,2) as date) from SYSIBM.SYSDUMMY1;

Additionally, If you want to run the query from MS SQL linked server to DB2(To display only 100 rows).

    SELECT top 100 * from OPENQUERY([Linked_Server_Name],
    'select cast(Left(''20151104'', 4)||''-''||substring(''20151104'',5,2)||''-''||substring(''20151104'', 7,2) as date) AS Dt 
    FROM SYSIBM.SYSDUMMY1')

Result after above query:

Dt
2015-11-04

Hope this helps for others.

In format function your can use timestamp_format function. Example, if the format is YYYYMMDD you can do it :

select TIMESTAMP_FORMAT(yourcolumnchar, 'YYYYMMDD') as YouTimeStamp 
from yourtable

you can then adapt then format with elements format foundable here

You can use:

select VARCHAR_FORMAT(creationdate, 'MM/DD/YYYY') from table name