Uniform SQL Date in Oracle SQL and SQL Server

Question

Like the title indicates, I'm trying to make an uniform query, which has compatible syntax for Oracle SQL and for SQL Server

The current syntax I have is written in Oracle and is:

SELECT * 
FROM TableA
WHERE CREATION_DATE = TO_DATE('12-09-2015', 'DD-MM-YYYY') 

When I try to build this in SQL Server, this doesn't work. The syntax of SQL Server is

SELECT * 
FROM TableA 
WHERE OrderDate='12-09-2015'

But this doesn't work in Oracle SQL...

So what is the uniform way to write this?

Answer 1

I am not sure if SQL Server supports ANSI DATE literal, but it is supported in Oracle. The default string literal format is YYYY-MM-DD.

DATE '2015-09-12'

so, if ANSI standard is supported in SQL Server too, then use it. It is simple.

Based on this link, I think you could use the above in both the databases.

WHERE OrderDate='12-09-2015'

Never do that. You are comparing a DATE with a STRING, you might be just lucky to get correct data depending on your locale-specific NLS settings. But, never rely on implicit data type conversion.