I'm currently working on a project were a client app is querying an Oracle database. We started development however on a Sql Server database, since we have more control over the schema. That is, we run our own Sql Server somewhere, whereas all Oracle databases are centrally managed.
So I knew in advance that the application needed to be 'database' independent. Using a db-independent data access layer and conforming to as much ansi sql as I could think of, I thought that should not be much of a problem. Well it is. Consider this: there's a field in our database 'description' which is a varchar (50 or something long, but that's not relevant). This field may contain a date, us-style formatted. And when it is a date, I would like to treat it as such. In my original query (for the sql server), I had the following code:
SELECT DISTINCT CAST(YEAR(PH.DESCRIPTION) AS VARCHAR) AS THEYEAR FROM
.....
Sql Server is appearantly clever enough to evaluate any string it can convert as a date and treat it as such. Not so in Oracle. Here's the new query:
SELECT
DISTINCT TO_CHAR(TO_DATE(SUBSTR(PH.DESCRIPTION,1,INSTR(PH.DESCRIPTION,' ')),'FMmm/FMdd/yyyy'),'yyyy') AS THEYEAR
FROM .....
That took me about an hour to figure out. Agreed, I don't have much experience with Oracle, and with statements like this, I prefer to keep it that way.