I’m currently working on a Java project that uses HSQLDB for local unit testing, but tqa and production run against an Oracle database. My project uses Hibernate and we are using an import.sql script to automatically populate the database for tests. This was working perfect until we did our first deployment to Oracle. All of the date code was broken because of the differences between date formats in the 2 database systems.
I struggled to find a common approach so I could keep my single import.sql statement, but I never found a solution. If you know of one, please let me know because it would simplify my process of configuring test data.
My solution was to create 2 SQL files. The files have the same format except for the date values. I’m manually editing the files when I need to add data, but I’m sure you could create a script or batch file to do the replacement automatically. My manual edits consist of a lote of find/replace, but it only takes a couple of minutes. As the test data gets larger, I’m sure I will be trying to automate this.
So here is my quick synopsis of date formats between HSQLDB and Oracle:
HSQL
Simple dates can be constructed as a string such as:
'2000-01-01'
However, for my sample code, I needed dates relative to the current date. In order to do that, I had to concat a string together. As we’ll see later, Oracle makes this much easier. Nonetheless, here is a sample that will create a date that is 60 days prior to today. The nice part about this approach is that HSQLDB takes care of adjusting the month and year values so you can just subtract whatever number you want from current_date.
year(current_date) || '-' || month(current_date) || '-' || (dayOfMonth(current_date)-60)
Oracle
Simple cates can be created as:
TO_DATE('2000-01-01', 'YYYY-MM-DD')
or this way if you use the standard Oracle format of YYYY-MMM-DD:
'2000-JAN-01'
For dates relative to day, simply use the following code. Again, the database takes care of adjusting the month and year values.
TO_CHAR(sysdate-60, 'DD-MON-YYYY')
