HOURS[*] syntactical issue 42000-200

Question

I am trying to execute this SQL query

SELECT * FROM GY.TABLE_NAME WHERE TEST_ID < 999999999 AND current_timestamp > TEST_GMTS + 20 HOURS

When I am expecting it, the response is

SQL error 42000, sql state : 42000 Syntax error in SQL statement "SELECT * FROM GY.TABLE_NAME WHERE TEST_ID < 999999999 AND current_timestamp > TEST_GMTS + 20 HOURS[*]"

Org.hiberante.exception.sqlgrammerexception: could not prepare statement

@data_henrik after adding brackets it is throwing 42001-200, expected "[, ::, AT, FORMAT, *, /, %....]" — Paruchuri Ratan Chowdary, Sep 24 '21 at 06:07
My bad, from is already there. I had corrected the question. — Paruchuri Ratan Chowdary, Sep 24 '21 at 07:42
please edit your question to add the full plain text exception detail, and specify your version+platform of Db2-server, and your version of hibernate. — mao, Sep 24 '21 at 07:44
Add the `CREATE TABLE GY.TABLE_NAME` statement to the question. — Mark Barinstein, Sep 24 '21 at 11:06

score 1 · Answer 1 · answered Sep 25 '21 at 03:26

For H2 (and for any other DBMS that supports standard INTERVAL data type) you need to use

SELECT * FROM GY.TABLE_NAME WHERE TEST_ID < 999999999
    AND CURRENT_TIMESTAMP > TEST_GMTS + INTERVAL '20' HOUR;

Unfortunately, it looks like this standard-compliant syntax isn't supported by DB2.

20 HOURS is DB2-specific.

It means you need to use different SQL for different databases.

The next version of H2 (2.0.*) will also support more exotic syntax

SELECT * FROM GY.TABLE_NAME WHERE TEST_ID < 999999999
    AND CURRENT_TIMESTAMP > TEST_GMTS + 20 HOUR;

(with HOUR only, HOURS are invalid). This syntax is accepted by DB2 too, so hypothetically you can compile H2 from its current sources and use this own build instead of released version if you really need to use the same SQL for both H2 and DB2, but current H2 is very different from 1.4.* and you may run into some other issue.

HOURS[*] syntactical issue 42000-200

1 Answers1