CODEWITHSUNDEEP
sqloracle-database
user2917239
user2917239

Reputation: 898

SQL to filter for records more than 30 days old

Suppose I have the following query:

select customer_name, origination_date
where origination_date < '01-DEC-2013';

I would like to select all customers that have an origination date older than 30 days. Is there a way in SQL (oracle, if specifics needed) to specify it in a more dynamic approach than manually entering the date so that I don't need to update the query every time I run it?

Thanks!

Upvotes: 0

Views: 13577

Answers (2)

Ismail Hawayel
Ismail Hawayel

Reputation: 2443

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.

Upvotes: 2

ProVega
ProVega

Reputation: 5914

Sure try something like this:

select customer_name, origination_date where 
origination_date >= DATEADD(day, -30, GETUTCDATE());

This basically says where the origination_date is greater or equal to 30 days from now. This works in Microsoft SQL, not sure but there is probably a similar function on Oracle.

Upvotes: 3

Related Questions