asked on
Delete Range of dates in MySQL
I have searched the internet and copied in at least 7 solutions that are advertised around, but I simply cannot get it to work.
Problem: I have 100,000 rows in a database - all with a date field populated. I want to delete all rows if the date is older than 14 days. Among many attempts, this was my last, but the SQL is bad. Any thoughts for a legit solution that actually works :)
DELETE * FROM hours WHERE ADJAPPLYDTM < DATE_SUB( 2013-12-30, INTERVAL 14 DAY )
I would paste all the others I tried, but it wouldn't do any good at this point since they didn't work and I don't remember them.
Problem: I have 100,000 rows in a database - all with a date field populated. I want to delete all rows if the date is older than 14 days. Among many attempts, this was my last, but the SQL is bad. Any thoughts for a legit solution that actually works :)
DELETE * FROM hours WHERE ADJAPPLYDTM < DATE_SUB( 2013-12-30, INTERVAL 14 DAY )
I would paste all the others I tried, but it wouldn't do any good at this point since they didn't work and I don't remember them.
MySQL ServerApache Web Server
Last Comment
Kevin Cross
What are some sample values of the ADJAPPLYDTM field, or more importantly can you verify the data type of the column?
ASKER
Yes, it is datetime as type of column.
Here is a format example: 2013-11-24 00:00:00
Here is a format example: 2013-11-24 00:00:00
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
PERFECT! This is what I needed. The CURDATE will make things easier and I must have been forgetting the semicolon accidentally. THANKS MUCH.
ASKER
One other question if you don't mind:
What would it look like to delete just the last two days as well? I will run it as a second SQL query obviously, but last two days as well would be nice to clean up....
What would it look like to delete just the last two days as well? I will run it as a second SQL query obviously, but last two days as well would be nice to clean up....
Do you mean you want to keep records between '2013-12-23' and '2014-01-04', removing everything else? Remember the "INTERVAL n DAY" portion of the code controls how you shift the original date, which in your case is today. If last two days refers to today and yesterday, then you really want "INTERVAL 1 DAY" whereas to grab Saturday (two days ago) as well you need "INTERVAL 2 DAY".
Anyway, the query would be:
Therefore, you can combine the queries into one:
If I did not understand you question correctly, you may want to consider created a related question. It will notify me as well as bring in additional Experts.
Regards,
Kevin
Anyway, the query would be:
DELETE
FROM `hours`
WHERE ADJAPPLYDTM >= DATE_SUB(CURDATE(), INTERVAL 1 DAY)
;
Therefore, you can combine the queries into one:
DELETE
FROM `hours`
WHERE (ADJAPPLYDTM < DATE_SUB(CURDATE(), INTERVAL 14 DAY)
OR ADJAPPLYDTM >= DATE_SUB(CURDATE(), INTERVAL 1 DAY))
;
If I did not understand you question correctly, you may want to consider created a related question. It will notify me as well as bring in additional Experts.
Regards,
Kevin
I want to run the script each day and it will delete anything older that 14 days based on the ADJAPPLYDTM column. the whole row goes based on that one field's value.