I want to find records in a (Oracle SQL) table using the creation date field where records are older than 30 days. It would be nice to find records using a operators like > but if anyone can suggest quick SQL where clause statement to find records older than 30 days that would be nice. Please suggest Oracle syntax as that is what I am using.
Asked
Active
Viewed 1.6e+01k times
1 Answers
75
Use:
SELECT *
FROM YOUR_TABLE
WHERE creation_date <= TRUNC(SYSDATE) - 30
SYSDATE returns the date & time; TRUNC resets the date to being as of midnight so you can omit it if you want the creation_date that is 30 days previous including the current time.
Depending on your needs, you could also look at using ADD_MONTHS:
SELECT *
FROM YOUR_TABLE
WHERE creation_date <= ADD_MONTHS(TRUNC(SYSDATE), -1)
OMG Ponies
- 325,700
- 82
- 523
- 502
-
4I prefer `creation_date <= TRUNC(SYSDATE) - interval '1' month` :) – Kirill Leontev Oct 05 '10 at 04:03
-
3@be here now: that's fine, as long as you don't mind if it fails with ORA-01839 "date not valid for month specified" in some cases, e.g. if creation_date is 31 March. – Jeffrey Kemp Oct 05 '10 at 04:38
-
1hm, I've always believed `interval` expression was designed to handle these situations correctly... – Kirill Leontev Oct 05 '10 at 04:57
-
Should we trunc the creation date as will like `WHERE TRUNC(creation_date) <= TRUNC(SYSDATE) - 30` ? – Salman Sep 29 '16 at 11:26