Convert special String into Date in H2

Question

There is a SQL function from Oracle to_date('26 Jul 2016, 05:15:58 AM','DD Mon YYYY, HH:MI:SS AM'), and it throws exception "Illegal pattern character 'o'" in H2.

How shall I change it to make it work in H2?

I don't know the platform, but is `Mon` the way you declare a month in that context? I'd bet not - I'd bet it's `MM` — Pekka, Jul 26 '16 at 09:32
@Pekka웃: `Mon` is the format pattern for the abbreviated month name in Oracle (e.g. `Feb` or `Jul`) — , Jul 26 '16 at 09:33

score 11 · Accepted Answer · answered Aug 02 '16 at 13:06

The equivalent function of TO_DATE() in H2 is PARSEDATETIME().

This is how you should use it with your sample data:

PARSEDATETIME('26 Jul 2016, 05:15:58 AM','dd MMM yyyy, hh:mm:ss a','en')

Be careful not to use HH:mm:ss otherwise the AM/PM detection will not work.

score 0 · Answer 2 · answered May 15 '17 at 20:59

Here is another example, integrating milliseconds:

The original value in Oracle is 13-APR-17 10.49.36.558000000 PM. First you need to get rid of the extra zeros and end up with 13-APR-17 10.49.36.558 PM then you can call

call PARSEDATETIME('13-APR-17 10.49.36.558000000 PM','dd-MMM-yy hh.mm.ss.SSS a','en');

Note the additional format parameter .SSS

Convert special String into Date in H2

2 Answers2

Linked