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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

You can use       select VARCHAR FORMAT creationdate   MM DD YYYY   from table name

[casting] Converting a string to a date in DB2

Examples related to casting

Examples related to db2

Examples related to date