Testing the Oracle to_date function

Question

I'm writing an integration test in Grails using GORM.

I want to do something like the following:

delete from Statistic 
where stat_date = TO_DATE(:month_year, 'MON-YYYY')

But I get the following error:

java.sql.SQLException: Unexpected token: TO_DATE in statement [delete from statistics where stat_date=TO_DATE(?, 'MON-YYYY')]

I think the error is caused by the in memory database used by GORM (is it H2?) not supporting the to_date function.

Any ideas on how to write the delete SQL so that it works in a test and in live?

As I only really care about the Month and Year one thought I had would be to delete the records where the stat_date is between the first and last date of the given month.

Can any one think of a better way?

Answer 1

This still comes up as number 1 on google searches so here's what worked for me.

My unit tests/local environment build and populate a schema using sql files. I created the following alias in the sql file

-- TO_DATE 
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); 
} 
';

Notice the use of single quotes instead of $$ in h2 user defined functions as that is the only format that worked for me.