Leafing through the DB/2 SQL Reference, I found a function called to_date( string-expression, format-string ), which converts strings to dates. Just what I needed, I thought, for converting string in arbitrary formats to dates.
Nope.
This what the manual says about the format-string parameter:
format-string A character constant that contains a template for how the string expression is to be interpreted as a timestamp value. The length of the format string must not be greater than 254… The content of format-string can be specified in mixed case.
Valid format strings are:
‘YYYY-MM-DD HH24:MI:SS’
And that’s it. There is ONE valid format string. Passing any other value for that parameter earns you this error message:
SQL0171N The data type, length or value of argument “2” of routine “SYSIBM.TO_DATE” is incorrect. SQLSTATE=42815
Very funny, IBM.
Had a huge day today converting our Hibernate application from DB/2 to Oracle 9i. The move seemed to highlight every single little shortcut we took through the whole development so far.
There were numerous little gotchas. Keith took the first one. Hibernate was using a single SELECT statement to read 146 columns spread across ten tables, half of which were outer joined. The generated SQL was 5923 bytes long. On DB/2, it was a performance problem; on Oracle it caused the JDBC driver to curl up its toes and die.
The most frustrating problem of the day was dates. It turns out that DB/2 and Oracle have very different ideas about how dates should be represented in literal SQL strings. We used literal dates quite heavily from our test cases. Turns out there is a representation of a timestamp that is valid in both Oracle and DB/2 (to_date(‘1962–08-11 12:00:00’, ‘YYYY-MM-DD HH24:MI:SS’)), but for straight dates there was no single string that worked in both SQLs. Prepared statements are fine—it’s just literal dates in SQL.
Hibernate has certainly lived up to its reputation—the generated SQL runs as well on Oracle as it does on DB/2. The only Hibernate problems we ran into were due to misconfigurations or misunderstandings, all easily corrected with a little help from Daniel.
Eclipse / WSAD deserve a mention here too. I would still be coding without Ctrl+Space, Ctrl+Shift+F and, especially, Ctrl+Shift+O.