Common SQL to compare dates in SQL Server and ORACLE

Question

I need to include a date comparison in my query where I compare a date string to a DATETIME column but i need it to work on both ORACLE and SQL Server and not have two separate queries. Are there any date comparissons which will work on both oracle and sql?

ORACLE:

Select * from MyTable where myDate > DATE '2013-04-10'

SQL Server:

Select * from MyTable where myDate > convert(DATETIME,'2013-04-10', 126)

Answer 1

This portability issue only applies with literals.

I do not believe there is a fully portable way to do this because SQL Server does not support the ANSI literal DATE keyword and all the CAST, CONVERT, TO_DATE, and date functions are not identical on all platforms.

Note that your second query can also be written as Select * from MyTable where myDate > '20130410'.

It would be nice if there was support in SQL Server for the ANSI DATE literal feature (DB/2 and Teradata both have this).

I could not find a Connect item on this, nor anything about why SQL Server doesn't support the ANSI DATE, TIME and TIMESTAMP literal keywords.

I'm wondering in your scenario, whether it would be possible to use a parameter instead?

The solution in Jack's comment Common SQL to compare dates in SQL Server and ORACLE will make this code portable, but you will have to have a non-portable scalar function. This might be a viable option for you - note that in SQL Server, you will need to prefix a scalar function with its schema - this might introduce another wrinkle between the Oracle code and SQL code if you can't make the schemas the same name (note in Oracle, the prefix could be the name of a package instead of a schema if you put the function inside a package)

Answer 2

Is defining your own user-defined functions allowed? You could create a function which abstracts the DBMS specific code and returns a literal 'True' or 'False' result.

[MSSQL User Defined Functions: ]http://msdn.microsoft.com/en-ca/library/ms186755.aspx

[Oracle User Defined Functions]http://ocs.oracle.com/cd/B19306_01/server.102/b14200

Oracle apparently does not allow user defined functions to return a Boolean type, so a string (or numeric) result is required.