We have a table with 3 columns (Order_ID, Item_Description, Order_Date). We add about 1000-5000 records to this table everyday. Everyday, we run a script that ensures that orders older than 30 days are deleted. We want to retain all the rows whose order_date is less than a month old and delete the rows that are older than 30 days.
Before deleting, we need want to select the data and validate it. So we would like to check which records satisfy the condition - Order_Date < (today's_date)-30. How do we achieve this? What can be the syntax? I understand that date(now) gives us today's date, but how do we get the date from 1 month ago?
I tried the following syntax, but it throws error. Can you please suggest the right syntax?
select * from Employees where Date_Joined<(date(now()-30)) allow filtering;
Should the column be defined as unix timestamp? Please share your thoughts.