How to fix Cannot parse "DATE" constant issue in H2 Database?

Question

I am porting my Java application which was developed for Windows to AIX Unix. On Windows it uses SQL Server for configuration. On AIX we are trying to use H2 database. Most of the code works but I am getting following error when executing query which has a datetime criteria.

org.h2.jdbc.JdbcSQLDataException: Cannot parse "DATE" constant "26-Jun-2019"; SQL statement:
SELECT EM_SCHEDULER_DAILY_POLL.* FROM EM_SCHEDULER_DAILY_POLL, EM_CONTROLLER WHERE EM_SCHEDULER_DAILY_POLL.CONTROLLER_ID =
EM_CONTROLLER.CONTROLLER_ID AND EM_SCHEDULER_DAILY_POLL.DATE_TIME
BETWEEN '26-Jun-2019' AND '26-Jun-2019 23:59:59' AND
POLLED_SUCCESSFULLY=0 AND EM_SCHEDULER_DAILY_POLL.CONTROLLER_ID=30
[22007-199]

This SQL works perfectly on SQL server but gives above exception on H2DB. How to solve this issue? I need both date and time in query.

Solution 1

Try using ISO date literals:

WHERE
    EM_SCHEDULER_DAILY_POLL.DATE_TIME >= '2019-06-26' AND
    EM_SCHEDULER_DAILY_POLL.DATE_TIME < '2019-06-27'

Note that since you are just looking for records on a single date, you could also try casting the column to date and doing a single comparison:

WHERE CAST(EM_SCHEDULER_DAILY_POLL.DATE_TIME AS DATE) = '2019-06-26'

As another comment, the first version I gave, with the two inequalities is sargable, meaning that the database should be able to use an index on the DATE_TIME column, while the second version, using the cast to date, probably cannot use an index. Therefore, the first version is the preferred way to go, if you ever need to tune or optimize your database.

Solution 2

You are passing a value with a time but H2 Date only don't have one. Just remove the time in your second constant.

'26-Jun-2019 23:59:59' --> '26-Jun-2019'

DATE The date data type. The format is yyyy-MM-dd.

Mapped to java.sql.Date, with the time set to 00:00:00 (or to the next possible time if midnight doesn't exist for the given date and timezone due to a daylight saving change). java.time.LocalDate is also supported on Java 8 and later versions.

Example:

DATE

Source :Data type of H2.

And since you just want one day (at least in that example), you can simply use :

DATE_TIME = '26-Jun-2019'

Note that Tim Biegeleisen's answer about ISO should be checked too, this format is not the best

Solution 3

Use TO_DATE function

Example - TO_DATE('01-12-2019','dd-MM-yyyy')

Insert into student(Id,Name,DOB) values(1, 'Abc', TO_DATE('01-12-2019','dd-MM-yyyy'))

Comments

[AxelH]

H2 Date don't have time like 26-Jun-2019 23:59:59 so it is not valid. You need to get rid of the BETWEEN and use DATE_TIME = 26-Jun-2019 (since you just want that date)

[Ram]

I tried this and it works, but I really need time component. I automatically ported database using RazorSQL. Should I use TIMESTAMP column instead of Date? I am new to H2, hence not familiar with its datatypes much.

[Tim Biegeleisen]

@Ram You don't need the time component, because your WHERE clause logic is saying you just want to match the single date 26-June-2019. If you have other logic requirements which you did not mention, then maybe edit your question and reveal them.

[Ram]

I see the error now. I guess there are subtle differences in database data types. Thanks for the help.

[Tim Biegeleisen]

Note that BETWEEN is inclusive of both ends of the expression. So use >= '26-Jun-2019', if that would work.