Geek Answers Handbook

Generic SQL for comparing dates in SQL Server and ORACLE

I need to include date comparison in my query, where I am comparing a date string with a DATETIME column, but I need it to work on both ORACLE and SQL Server and not have two separate queries. Are there any time comparisons that will work with 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)
+6
source share
2 answers

This portability problem applies only to literals.

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

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

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

I could not find the Connect element on this, nor anything about why SQL Server does not support the literal words ANSI DATE, TIME and TIMESTAMP.

I am wondering in your scenario, is it possible to use a parameter instead?

The solution in Jack's comment Generic SQL for comparing dates in SQL Server and ORACLE will make this code portable, but you will have to have a scalable function that is not portable. This may be a viable option for you - note that in SQL Server you will need to prefix the scalar function with your schema - this can lead to another wrinkle between the Oracle code and the SQL code if you cannot make the schemas the same (note that in Oracle the prefix may be the name of the package instead of the scheme if you place the function inside the package)

+5
source

Does it allow you to define your own custom functions? You can create a function that abstracts a specific DBMS code and returns the literal "True" or "False".

[MSSQL custom functions:] http://msdn.microsoft.com/en-ca/library/ms186755.aspx

[Oracle Custom 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.

+1
source

More articles:


All Articles