Skip to content
Advertisement

SQL query to delete records when the difference between two dates is greater than a certain value

I would like to delete records when the difference between SYSDATE and a TIMESTAMP (6) field of my table is greater than 10 days. I have created the following query:

but i get the following error:

am i creating the query correctly?

Advertisement

Answer

am i creating the query correctly?

No, you cannot refer to an alias in the SELECT clause in the filter condition of the same statement.

Additionally, when you subtract a TIMESTAMP from a DATE you will get the result as an INTERVAL data type; so you need to compare on that rather than a NUMBER (which would be the result if you compared DATE - DATE).

You want:

db<>fiddle here

User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement