0

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?

MarredCheese
  • 17,541
  • 8
  • 92
  • 91
user3130997
  • 11
  • 1
  • 1
  • 1
  • So, if your date is formatted like `DD-Mon-YY`, why are you trying to parse it as `YYYYMMDD`? The date/time format specification that is valid for your version of DB2 is fully described in the manual. – mustaccio Dec 24 '13 at 14:43

3 Answers3

4

I think you're looking for:

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

Aside: TO_DATE is just a synonym for TIMESTAMP_FORMAT.

bhamby
  • 15,112
  • 1
  • 45
  • 66
  • +1, and pray that somebody wasn't using a different locale at some point (best case in that situation is that it borks on those rows - worst case is that it silently converts them to something invalid). This is the second reason you're not supposed to store dates in this format (the first being that it's not nicely sortable). – Clockwork-Muse Jan 14 '14 at 09:54
1

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

Larry Benton
  • 11
  • 1
0

Try that

TO_DATE(@your_string, 'yyyy/mm/dd')
Gautam Beri
  • 157
  • 1
  • 6