6

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.

AxelH
  • 14,325
  • 2
  • 25
  • 55
Ram
  • 1,225
  • 2
  • 24
  • 48
  • 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) – AxelH Jun 26 '19 at 05:15

4 Answers4

8

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.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • 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. – Ram Jun 26 '19 at 05:36
  • @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. – Tim Biegeleisen Jun 26 '19 at 05:37
  • I see the error now. I guess there are subtle differences in database data types. Thanks for the help. – Ram Jun 26 '19 at 05:42
1

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

AxelH
  • 14,325
  • 2
  • 25
  • 55
1

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

Khyati Elhance
  • 662
  • 6
  • 11
0

There is a converter PARSEDATETIME() fuction.

For example if the date is 12/03/2013 we need to convert as PARSEDATETIME('12/03/2013','dd/MM/yyyy') check this SO.

The SQL statement look like Insert into invoice(id, invoice_date) values(1, PARSEDATETIME('12/03/2013','dd/MM/yyyy'))

abdella
  • 490
  • 5
  • 11