Menu
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.
510
Valid DATE Values. A valid DATE value must fall between January 1, 1000, and December 31, 9999. It must conform to one of three styles: numeric, packed numeric, or month name. You can mix these styles throughout a session.
Use the @DATEDIFF function to calculate the difference between two dates or datetimes, in days or seconds. The difference between the specified dates. Valid values can be: DD , which computes the difference in days.
To calculate the difference between the timestamps in Oracle, simply subtract the start timestamp from the end timestamp (here: arrival - departure ). The resulting column will be in INTERVAL DAY TO SECOND . The first number you see is the number of whole days that passed from departure to arrival .
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) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
