Why can't H2 database parse a timestamp?

Question

My project is using Oracle database, and everything works just fine. For the purpose of testing I set up an H2 db. The following query now throws an error:

"SELECT * FROM ERESIS.ECH_HISFAB f WHERE f.FG_ETAT = 'A' AND TO_DATE(DT_INS) > '30-AUG-18' ORDER BY f.CD_MAT"

error:

Cannot parse "TIMESTAMP" constant "30-AUG-18"; SQL statement:

I can fix the error by setting up the string like TO_DATE('30-AUG-2018'), but changing the query kind of defeats the purpose since I already am sure the query works (but I need it to test the service). Is there any way to bypass this error without changing the query?

There is a difference between `DATE` and `TIMESTAMP`. This link might help - https://stackoverflow.com/questions/19141030/difference-between-oracle-date-and-timestamp. — Nik, Jul 30 '21 at 13:54
Why are you storing DATE values in a `varchar` column? That is really bad thing to begin with. — , Jul 30 '21 at 13:55
@Nik Any ideas how does the server than actually work or am I missing something? — aratata, Jul 30 '21 at 13:58
@a_horse_with_no_name Not really my project, I just have to write tests for it. — aratata, Jul 30 '21 at 13:59
@aratata Please check if this helps - https://stackoverflow.com/questions/14138532/h2-database-string-to-timestamp — Nik, Jul 30 '21 at 14:02
@Nik I was actually searching for a way to test the service without changing the query, seems that I have to make adjustments. Thanks for the help anyways. — aratata, Jul 30 '21 at 14:29

score 0 · Answer 1 · answered Jul 30 '21 at 16:05

0

parsedatetime() should be able to convert string to TIMESTAMP, please try using -

"SELECT * FROM ERESIS.ECH_HISFAB f WHERE f.FG_ETAT = 'A' AND TO_DATE(DT_INS) > parsedatetime('30-AUG-2018', 'dd-MMM-yyyy') ORDER BY f.CD_MAT"

answered Jul 30 '21 at 16:05

Nik

173
2
17

score 0 · Answer 2 · answered Dec 13 '21 at 09:47

I had a similar issue with H2 (1.4.200), it has just one format for TO_DATE(input) method "DD MON YYYY". After a debug I found that H2 uses an enum for date format when it's not provided as second parameter: org.h2.expression.function.ToDateParser.ConfigParam

TO_DATE("DD MON YYYY")

I did a solution to override it using reflection so that the sql code does not change.

In my unit test I created a utility method used to instantiate the workaround on @BeforeClass

  private static boolean h2WorkaroundApplied = false; //utility to apply workaround just one time 
  protected static synchronized void applyH2ToOracleCompatibilityWorkaround() {
    if (!h2WorkaroundApplied) {
      fixH2ToDateFormat(); //apply to_date  workaround
      h2WorkaroundApplied = true; //disable future changes of same workaround
    }
  }


private static void fixH2ToDateFormat() {
    try {
      Class<?> classConfigParam = Arrays.stream(ToDateParser.class.getDeclaredClasses())
          .filter(c -> "ConfigParam".equals(c.getSimpleName()))
          .findFirst()
          .orElseThrow(); //get the enum inner class

      Object toDateEnumConstant = Arrays.stream(classConfigParam.getEnumConstants())
          .filter(e -> "TO_DATE".equals(((Enum) e).name()))
          .findFirst()
          .orElseThrow(); //get the enum constant TO_DATE

      Field defaultFormatStr = classConfigParam.getDeclaredField("defaultFormatStr"); //get the enum private field defaultFormatStr
      defaultFormatStr.setAccessible(true);
      defaultFormatStr.set(toDateEnumConstant, "YYYY-MM-DD"); //change with my date format used by production oracle DB.

    } catch (Exception e) {
      throw new UnsupportedOperationException(
          "The current H2 version doesn't support this workaround. Tested with version 1.4.200", e);
    }
  }

Why can't H2 database parse a timestamp?

2 Answers2