Oracle SQL Where clause to find date records older than 30 days

sql oracle date-arithmetic
anwarma picture anwarma · Oct 5, 2010 · Viewed 134.9k times · Source

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.

Answer

OMG Ponies picture OMG Ponies · Oct 5, 2010

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)

Related questions

Oracle DateTime in Where Clause?

I have sql something like this: SELECT EMP_NAME, DEPT FROM EMPLOYEE WHERE TIME_CREATED >= TO_DATE('26/JAN/2011','dd/mon/yyyy') -> This returns 10 rows and TIME_CREATED = '26-JAN-2011' Now when i do this …

Read More
Oracle date "Between" Query

I am using oracle database. i want to execute one query to check the data between two dates. NAME START_DATE ------------- ------------- Small Widget 15-JAN-10 04.25.32.000000 PM Product 1 17-JAN-10 04.31.32.000000 PM select * from <TABLENAME> where start_date BETWEEN '15…

Read More
How to subtract hours from a date in Oracle so it affects the day also

I'm trying to subtract date from Oracle so it even effect the day as well. For example, if the timestamp is 01/June/2015 00 hours and if I subtract 2 hours, I want to be able to go to to 31/May/2014 22 hours. I …

Read More