Oracle SQL Where Clause to Find Date Records Older Than 30 Days

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

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)

Oracle select statement where date is greater than 30 days

your select statement
WHERE date_entered < TRUNC(SYSDATE)-30

Find records older than 30 days in oracle sql

For OBIEE reports:

timestampadd(SQL_TSI_DAY,-30,CURRENT_DATE) 

(OR)

SYSDATE, cast(EVALUATE('SYSDATE-30') as timestamp) 

For SQLPLUS, Try this- SYSDATE will return the current date; subtract 30 for the number of days:

SELECT * 
FROM   TABLE
WHERE  DATE_FIELD < SYSDATE-30;

References:

http://oracle.ittoolbox.com/groups/technical-functional/oracle-bi-l/how-to-use-sysdate-function-in-obiee-reports-4291644

SQL Query to fetch data from the last 30 days?

SELECT productid FROM product WHERE purchase_date > sysdate-30

SQL to filter for records more than 30 days old

in Oracle, when you subtract dates, by default you get the difference in days, e.g.

select * from my_table where (date_1 - date_2) > 30

should return the records whose date difference is greater than 30 days.
To make your query dynamic, you parameterize it, so instead of using hard coded date values, you use:

select * from my_table where (:date_1 - :date_2) > :threshold 

If you are using oracle sql developer to run such a query, it will pop up a window for you to specify the values for your paramteres; the ones preceded with colon.

Fetch data ageing for more than 90 days in oracle

I want to get data in oracle for more than 90 days from todays date

To me, it looks like

and b.rejected_date <= TRUNC(sysdate)-90

Subtract 30 days from current date in where clause

ADS allows you to do straight math on dates (if you've got an actual date column in the table), so you can use

WHERE datecol between curdate() - 30 and curdate()

---

Related Topics