Convert dd-mon-yy string to date in DB2

Question

in DB2 I've got date values stored as varchar in the form 'DD-Mon-YY' (e.g. 25-Jun-13). I'd like to convert these into DB2 compatible date formats using the TO_DATE function but every date conversion format I've tried gives me an error.

e.g. I've tried TO_DATE('25-Jun-13', 'YYYYMMDD') and TO_DATE('25-Jun-13', DDMMYYYY) and I always get something like "25-Jun-13" cannot be interpreted using format string "DDMMYYYY" for the TIMESTAMP_FORMAT function.

Does anyone know if there is a format string that I can use?

Answer 1

I think you're looking for:

DATE(TIMESTAMP_FORMAT(@your_date, 'DD-Mon-YY'))

Aside: TO_DATE is just a synonym for TIMESTAMP_FORMAT.

Answer 2

Try that

TO_DATE(@your_string, 'yyyy/mm/dd')

Answer 3

to_date('string', 'template')

the date format of the string must match the template

MM is month as number MON is MONth as abbreviation

Example

select to_date('20130625', 'YYYYMMDD') as YYYYMMDD
,to_date('25-Jun-2013', 'DD-MON-YYYY') as DDMONYYYY
from sysibm.sysdummy1

YYYYMMDD DDMONYYYY

---

2013-06-25 00:00:00.0 2013-06-25 00:00:00.0