David Borneman David Borneman - 5 months ago 13
SQL Question

Convert SQL Server code to Oracle please?

Having a bit of a issue with a SQL conversion from SQL Server to Oracle.

We are passing in a datetime value (in this example just 1900-01-01) and we need to select all rows that have a modified date greater than 2 days before the date passsed in. Here is the SQL syntax that works:

SELECT *
FROM TABLENAME
WHERE CAST(LAST_MODIFIED AS DATE) > CAST(DATEADD(dd, -2, '1990-01-01') AS DATE);


Where
LAST_MODIFIED
is a column, and the
'1900-01-01'
is a value being injected to the SQL String in C# prior to it being executed. Before being asked, we prefer not to subtract the 2 days from the date before passing it to the SQL :)

So what we need then is just the above query converted to Oracle syntax... We have tried a couple things and it fails :(

Thanks,

Dave

Answer

You can write this in Oracle as:

SELECT *
FROM TABLENAME
WHERE TRUNC(LAST_MODIFIED) > (DATE '1990-01-01') - 2

Notes:

  • In Oracle, DATE includes a time component, so casting to a date does nothing.
  • Oracle supports various ways to include a date/time constant. I prefer the keyword DATE with the ISO/ANSI standard date format YYYY-MM-DD.
  • The - 2 is perhaps more accurately written as - interval '2' day. However, the interval notation is new(ish) to Oracle.

And, it is better in either database to write this without modifying last_modified_date:

SELECT *
FROM TABLENAME
WHERE LAST_MODIFIED >= (DATE '1990-01-01') - 1

Removing the time component is not needed, with the right date comparison.