Function "TO_DATE" not found in H2 database

Question

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:

Answer 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

Answer 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.

Answer 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.

Answer 4

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

Changelog ticket