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:
WHERE CAST(LAST_MODIFIED AS DATE) > CAST(DATEADD(dd, -2, '1990-01-01') AS DATE);
You can write this in Oracle as:
SELECT * FROM TABLENAME WHERE TRUNC(LAST_MODIFIED) > (DATE '1990-01-01') - 2
DATEincludes a time component, so casting to a date does nothing.
DATEwith the ISO/ANSI standard date format YYYY-MM-DD.
- 2is 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
SELECT * FROM TABLENAME WHERE LAST_MODIFIED >= (DATE '1990-01-01') - 1
Removing the time component is not needed, with the right date comparison.