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

user_2756820 0
Viewed 13180

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.

h2 jdbc sql java
3 Answers

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.

user_18632290

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

user_43914500

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'))

user_47915760
Related