Converting a string to a date in DB2

Question

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

Answer 1

Based on your own answer, I'm guessing that your column has data formatted like this:

'DD/MM/YYYY HH:MI:SS'

The actual separators between Day/Month/Year don't matter, nor does anything that comes after the year.

You don't say what version of DB2 you are using or what platform it's running on, so I'm going to assume that it's on Linux, UNIX or Windows.

Almost any recent version of DB2 for Linux/UNIX/Windows (8.2 or later, possibly even older versions), you can do this using the TRANSLATE function:

select 
   date(translate('GHIJ-DE-AB',column_with_date,'ABCDEFGHIJ'))
from
   yourtable

With this solution it doesn't matter what comes after the date in your column.

In DB2 9.7, you can also use the TO_DATE function (similar to Oracle's TO_DATE):

date(to_date(column_with_date,'DD-MM-YYYY HH:MI:SS'))

This requires your data match the formatting string; it's easier to understand when looking at it, but not as flexible as the TRANSLATE option.

Answer 2

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.