CODEWITHSUNDEEP
javasqlhsqldboracle12c
Ranjith Reddy
Ranjith Reddy

Reputation: 183

Query that works in oracle and HSQL

Hello all I have a query in my code which is working with oracle 12c.

SELECT * 
FROM TABLE-A 
     join TABLE-B on TABLE-A.id=TABLE-B.id 
where TABLE_B.ISRT_TS BETWEEN TO_TIMESTAMP ('10-JUN-17 04.00.00.000000000 AM','DD-Mon-RR HH:MI:SS.FF9 AM') AND TO_TIMESTAMP('10-Sep-17 03.59.59.999999999 AM', 'DD-Mon-RR HH:MI:SS.FF9 AM')

but when I execute same query in HSQL I am getting exception. can any one suggest what to change in the existing query to work in oracle and hsql.

Error in hsql:data exception: invalid datetime format: 9 AM
                       Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.

HQL vesrsion: 1.8.0.10

Upvotes: 0

Views: 162

Answers (1)

user330315
user330315

Reputation:

to_timstamp() is not part of the SQL standard, so it's not a surprise that HSQL does not understand it.

A portable way of writing your condition is to use ANSI timstamp literals:

select * 
from table_a 
   join table_b on table_a.id = table_b.id
where table_b.isrt_ts between timestamp '2017-06-10 04:00:00' and timestamp '2017-09-10 03:59:59.99999'

I don't know if the ancient and outdated version 1.8 supports that - but you should really upgrade to a current version.

Upvotes: 1

Related Questions