'Function "TO_DATE" not found in H2 database

I have a SQL statement and trying execute with H2 in-memory database in Java. The following exception thrown.

SQL:

SELECT ACCT_RULE_ID, ACCT_ACTION_ID 
  FROM ACCT_RULE 
 WHERE (ACCT_ACTION_ID = ?) 
   AND (START_DATETIME <= to_char(?, 'mm/dd/yyyy HH:MI:SS AM')) 
   AND (STOP_DATETIME > to_char(?, 'mm/dd/yyyy HH:MI:SS AM'))

Replacing first parameter with Id and second and third parameter with new Date() value.

Exception:
Caused by: org.h2.jdbc.JdbcSQLException: Function "TO_DATE" not found; SQL statement:
javasqlh2to-date


Solution 1:[1]

you should be able to create your own to_date function 

drop ALIAS if exists TO_DATE; 
CREATE ALIAS TO_DATE as '
import java.text.*;
@CODE
java.util.Date toDate(String s, String dateFormat) throws Exception { 
  return new SimpleDateFormat(dateFormat).parse(s); 
} 
'

Of course you could also just use parsedatetime() per David Small's answer

Solution 2:[2]

One way to remove the time portion from a date-time field in H2, is to format the field as a string and then parse it. This worked for me:

PARSEDATETIME(FORMATDATETIME(field_name, 'yyyy-MM-dd'), 'yyyy-MM-dd')

H2's parse and format date functions follow the java.text.SimpleDataFormat semantics.

Yes, it is NOT super optimized. This is fine for our needs since we only use H2 for unit tests.

Solution 3:[3]

H2 database does not have TO_CHAR() function. But H2 database does have sysdate, dual, varchar2 which makes writing oracle query that will run on H2 database quite easy. So you can write a function instead which will H2 database function alias for making it handle date/timestamp with format. TO_CHAR(sysdate, 'DD/MM/YYYY HH24:MI:SS') can be used in H2 database.

Solution 4:[4]

Despite the lack of documentation there are TO_DATE function in PostgreSQL compatibility mode since 2.0.204.

Changelog ticket

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 EoinS
Solution 2 Jacob van Lingen
Solution 3 TKV
Solution 4

Related Questions