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:

I'm using java.util.Date() – user3268428 Feb 04 '14 at 05:02 — user3268428, Feb 04 '14 at 05:02

score 14 · Answer 1 · answered Dec 19 '18 at 19:40

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

score 5 · Answer 2 · edited Jun 08 '22 at 14:50

5

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.

edited Jun 08 '22 at 14:50

Jacob van Lingen

8,989
7
48
78

answered Mar 25 '16 at 14:23

David Small

156
2
3

score 2 · Answer 3 · answered Feb 04 '14 at 05:04

2

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.

answered Feb 04 '14 at 05:04

TKV

2,533
11
43
56

1

I believe Tijo was suggesting that you use a [User Defined Function](http://www.h2database.com/html/features.html#user_defined_functions),. More details here: http://stackoverflow.com/a/10903425/443633. Which does work. Thanks Tijo! – michaelok Dec 20 '15 at 22:59

Zed · Answer 4 · 2022-02-17T15:55:22.750

1

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

Changelog ticket

edited Feb 17 '22 at 15:55

answered Oct 28 '15 at 16:08

Zed

41
3

the link is dead – ihebiheb Jan 12 '22 at 20:18
Nevermind. In the meantime they fixed it. – Zed Feb 17 '22 at 15:58

Function "TO_DATE" not found in H2 database

4 Answers4

Linked