How do I compare diferently formatted dates in sql db2?

Question

One column is formatted like this: dd/mm/yyyy The other is formatted like this: yyyymmdd How do I create a select statement which lets me compare these two columns? They are both varchar. I'm running a db2 database.

Answer 1

I DB2, you can use TO_DATE() to convert both string dates to TIMESTAMP datatypes (note that TO_DATE is a synonym for the TIMESTAMP_FORMAT scalar function).

You can then safely compare them:

to_date(col1, 'dd/mm/yyyy') = to_date(col2, 'yyyymmdd')

Answer 2

I solved it like this. I used substr:

substr(COLUMN_NAME, 7,4) || substr(COLUMN_NAME,4,2) || sub-str(COLUMN_NAME,1,2)

_{Posted on behalf of the question asker}