TO_CHAR(sysdate, 'DD/MM/YYYY HH24:MI:SS') for H2 database

TO_CHAR(sysdate, 'DD/MM/YYYY HH24:MI:SS') can be used in H2 database. This Date Conversion from Oracle Format to java format can be used for normal java applications also.

This is an example using H2 database function alias for making it handle date/timestamp with format specified as Oracle Date format string. Since H2 database does not have TO_CHAR() function this is how I use my oracle queries for testing on H2 database. H2 database does have sysdate, dual, varchar2 which makes writing oracle query that will run on H2 database quite easy.

DROP alias if exists TO_CHAR;
CREATE alias TO_CHAR as $$
import java.text.SimpleDateFormat;
import java.util.Date;
@CODE
String toChar(String date, String pattern) throws Exception {
pattern = pattern.replaceAll("YY","yy");
pattern = pattern.replaceAll("DD","dd");
pattern = pattern.replaceAll("HH24|hh24","HH");
pattern = pattern.replaceAll("HH?!24|hh?!24","KK");
pattern = pattern.replaceAll("MON|mon","MMM");
pattern = pattern.replaceAll("MI|mi","mm");
pattern = pattern.replaceAll("SS|ss","ss");
pattern = pattern.replaceAll("AM|PM","aa");
SimpleDateFormat sm = new SimpleDateFormat(pattern);
java.util.Date dt;
  if(date.length() > 10)dt = java.sql.Timestamp.valueOf(date);
else
 dt = java.sql.Date.valueOf(date);
return sm.format(dt);

}
$$
create table test(bday timestamp);
insert into test values(today);
select TO_CHAR(bday,'DD/MM/YYYY HH24:MI:ss') from test;

create table test2(bday DATE);
insert into test2 values(today);
select TO_CHAR(bday,'DD/MM/YYYY HH24:MI:ss') from test2;