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

Rundell

i wish our dates were strings in our DB2...they're integers! and there's 6 number dates (YYMMDD) (and 8 number dates (YYYYMMDD) - the solution to the Y2K issue!)....so in the 6 number dates the value 111 is really January 11, 2000...talk about messy conversions! – Eleneeleni

Could you post sample data from the SETTLEMENTDATE.VALUE column? – Corrinacorrine

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.

Vick

Hi Ian, thanks for this answer. I knew there must be a better way than what I was doing. Thanks! – Rundell

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.

Fabulist

You could also do the following if your date column is an integer datatype formatted like YYYYMMDD (e.g. 20230522), and not a character string: SELECT DATE(TIMESTAMP_FORMAT(CHAR(your_int_date_column),'YYYYMMDD')) AS "DatefromInteger" – Martita

Answer 3

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

Hornback

Note, if your date column is an integer datatype like YYYYMMDD, and not a character string (e.g. 20230522), you will need to use something like: SELECT DATE(TIMESTAMP_FORMAT(CHAR(your_int_date_column),'YYYYMMDD')) AS "DatefromInteger" – Martita

Answer 4

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!

Rundell

While this hacky way of doing things works, please see Ian Bjorhovde's answer, which I have accepted above! – Rundell

Answer 5

Note, if your date column is an integer datatype like YYYYMMDD, and not a character string (e.g. 20230522), you will need to use something like:

SELECT

    DATE(TIMESTAMP_FORMAT(CHAR(your_int_date_column),'YYYYMMDD'))  AS "DatefromInteger"

Martita

Answer 6

You can use:

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

Gargantuan

I believe they're asking for string to date and not date to string – Orozco