DELETE rows within date range (based on current_date)

[togi_one]

I have just started running a website for an outbound tour operator's online booking system. I needed to be able to delete rows by departure date within several days of departure (to stop people searching and/or booking a departure date on the same day or within too few days to safely organise all the arrangements for a packaged holiday in time)

Here is my code:

DELETE FROM `table_name`
WHERE `column_name` BETWEEN CURDATE() and DATE_ADD(CURDATE(), INTERVAL 3 DAY)

Also to delete old data:

DELETE FROM `table_name`
WHERE `column_name` <= CURDATE()

Hope someone find this helpful :thumbsup:

[Old Pedant]

The problem with this code is that if your `column_name` field is a DATETIME field that *DOES* have both date and time data, then the first of those queries will not delete any data for that 3-days-from-today date unless its time portion is exactly midnight.

That's because CURDATE() really returns a value that is (example) '2009-12-15 00:00:00' so if your field contains '2009-12-18 13:15:27' it will *not* be matched by that BETWEEN (because adding 3 days to CURDATE() gives '2009-12-18 00:00:00').

Now, maybe that's exactly what you want.

But if not--if you want to delete records from *any* time on the 18th--then there are a couple of easy "fixes":

DELETE FROM `table_name`
WHERE DATE(`column_name`) BETWEEN CURDATE() and DATE_ADD(CURDATE(), INTERVAL 3 DAY)

The DATE() function in MySQL *removes* the time part of a date plus time value.

Or you could do:

DELETE FROM `table_name`
WHERE `column_name` BETWEEN >= CURDATE()
AND `column_name` < DATE_ADD(CURDATE(), INTERVAL 4 DAY)

The latter version will likely perform better if your column is indexed, as it doesn't have to apply a function to each value in the index or table and can make the decision solely on the basis of the "as-is" value.

[Coyote6]

You may want to just create a disable field instead of actually deleting the record. Say later on down the road the owner wants to see all of the departures for the past 6 months for record keeping purposes. If the record is deleted he's out of luck, but if they are disabled then you simple just query the database. Then once your database starts getting full (if it ever does) you could create an archive of the old records and save them some place else outside the database.