Search code examples
sqloracleh2sql-timestamp

Convert special String into Date in H2

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?

Solution

  • 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.