Function "TO_DATE" not found in H2 database

java sql h2 to-date
user3268428 picture user3268428 · Feb 4, 2014 · Viewed 21.2k times · Source

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

David Small picture David Small · Mar 25, 2016

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

Related questions

Can I have H2 autocreate a schema in an in-memory database?

(I've already seen the H2 database In memory - Init schema via Spring/Hibernate question; it is not applicable here.) I'd like to know if there's a setting in H2 that will allow me to auto-create a schema upon connecting …

Read More
H2: how to tell if table exists?

I'm trying to write Java code that checks to see if an H2 table exists: if it doesn't exist, it first executes a CREATE TABLE query that creates the table before it proceeds any further. I followed the advice in …

Read More
How do I allow multiple users to connect to my H2 database simultaneously?

I am using H2 for database management, and this is what I would like to do: I would like to allow multiple users to access a database at the same time. I've read a bit about "MULTI_THREADED=TRUE", "LOCK_…

Read More