How do i convert a date in varchar (1-Jan-2020) to date type in DB2 sql?

Question

I have a column with dates as below in my db2 table.

4-Jan-2020
7-Sep-2020
7-Dec-2020

I want to convert them to

2020-01-04
2020-09-07
2020-12-07

respectively. How do I achieve this? Thanks.

Answer 1

On recent versions of DB2, use TO_DATE:

SELECT DATE(TO_DATE(date_col, 'DD-MON-YYYY'))
FROM yourTable;

Data:

WITH yourTable AS (
    SELECT '4-Jan-2020' AS date_col UNION ALL
    SELECT '7-Sep-2020' UNION ALL
    SELECT '7-Dec-2020'
)