Testing the Oracle to_date function

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.*;@CODEjava.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.

I had to adjust bluesman's answer in order to make it work for the date formats that are commonly used in our Oracle sql.

This version supports dateFormats like 'DD-MON-YYYY'

-- TO_DATEdrop ALIAS if exists TO_DATE;CREATE ALIAS TO_DATE as 'import java.text.*;@CODEjava.util.Date toDate(String s, String dateFormat) throws Exception {  if (dateFormat.contains("MON")) {      dateFormat = dateFormat.replace("MON", "MMM");  }  if (dateFormat.contains("Y")) {      dateFormat = dateFormat.replaceAll("Y", "y");  }  if (dateFormat.contains("D")) {      dateFormat = dateFormat.replaceAll("D", "d");  }  return new SimpleDateFormat(dateFormat).parse(s);}';

I found the tips on this blog post http://javatechniques.com/blog/dateformat-and-simpledateformat-examples/ helpful in figuring out how to translate the Oracle date formats into SimpleDateFormat's formats.

Yes, H2 doesn't support TO_DATE, it's in 1.4.x roadmap. Instead you can use the EXTRACT function that exists both in Oracle DB and H2.