H2 Database : Cannot parse "DATE" constant "31-JAN-20" while inserting a new record

Question

Consider the following DDL I am using to create an H2 database table in one of my JUnit test cases :

CREATE TABLE "CONFIG_INFO" 
   (    "ID" VARCHAR2(12 BYTE), 
        "RUN_DATE" DATE, 
   );

The class that I am writing a unit test for tries to insert a record in this table. I can see that the following query is executed for inserting a record :

insert into CONFIG_INFO(ID,RUN_DATE) values (?,?) 

However, the insertion fails with the following exception :

org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [insert into CONFIG_INFO(ID,RUN_DATE) values (?,?)]; Cannot parse "DATE" constant "31-Jan-20"; 

I looked around and found that this issue usually occurs when you have a time component as part of the date; however, debugged the code and I can see that the Java code is passing "31-Jan-20" with no time stamp component as can also be seen from the error message above.

How can I resolve this error?

Answer 1

You'll need to use the H2 function PARSEDATETIME() to format the date in the format that the database expects.

In the second answer here you'll see an example that uses almost your exact format.

Convert special String into Date in H2

Or reformat your data into the ISO standard which in your case should be yyyy-MM-dd

Answer 2

java.time

The best solution is to use smart objects rather than dumb strings to exchange values with your database. Specifically, use the modern java.time classes.

Your inputs use a month name in all-uppercase. This does not conform with localization for English, at least not in US or UK English. So we must use DateTimeFormatterBuilder to get a DateTimeFormatter that tolerates all uppercase.

DateTimeFormatterBuilder builder = new DateTimeFormatterBuilder().parseCaseInsensitive().appendPattern( "dd-MMM-uu" ) ;
DateTimeFormatter formatter = builder.toFormatter() ;

Parse your input string.

String input = "31-JAN-20" ;
LocalDate localDate = LocalDate.parse( input , formatter ) ;

Trap for DateTimeParseException in case of faulty inputs.

Pass to the database using a JDBC driver compliant with JDBC 4.2 or later.

myPreparedStatement.setObject( … , LocalDate ) ;

Retrieve.

LocalDate localDate = myResultSet.getObject( … , LocalDate.class ) ;

Tip: Educate the publisher of your data about the ISO 8601 standard formats for exchanging date-time values as text.