CODEWITHSUNDEEP
phpmysql
Nina Morena
Nina Morena

Reputation: 255

MySQL Delete Row That Is Older Than The Current Date

I am trying to delete events in my database that have a start date older than the current day.

I've used the NOW statement and it deleted all of the content within my table.

The database is updated daily with events and I want to delete the events that have passed.

Here is a sample of my sql statement:

mysql_query("DELETE FROM Detroit WHERE Detroit.startDate < CURDATE()");

startDate is the name of the column in the db where all of the date information is stored.

The dates appear as Fri, 25 Apr 2014 19:00:00. When I use the CURDATE or NOW date options within my statement, the whole table is deleted. How do I delete the rows with the dates older than the current date?

Upvotes: 0

Views: 5467

Answers (3)

fthiella
fthiella

Reputation: 49049

I suspect that your startDate column is not a datetime field, but it's a varchar instead.

This query should work:

DELETE FROM Detroit
WHERE STR_TO_DATE(startDate, '%a, %e %b %Y') < CURDATE()

Or you could try to substitute %e with %d. However, it is always a better idea to use a DATETIME column and not a VARCHAR column to store date and times, so you should create a new column startDatedt and update your table this way:

UPDATE Detroit
SET startDatedt = STR_TO_DATE(startDate, '%a, %e %b %Y %H:%i:%S')

and then you could just use date and time functions to delete the rows that you need:

DELETE FROM Detroit WHERE startDatedt < CURDATE()

Please have a look here to see how to compose a date format string.

Upvotes: 0

Mohammad Saberi
Mohammad Saberi

Reputation: 13166

Try this code and let me know its result please: 

mysql_query("DELETE FROM Detroit WHERE DATEDIFF(CURDATE(),startDate) > 0");

Upvotes: 0

miny1997
miny1997

Reputation: 478

If you're working with the UNIX Timestamp(Seconds after the 1st january 1970, this format is always in UTC), you can use this code:

mysql_query("DELETE FROM Detroit WHERE Detroit.startDate < ".time());

Let me know if you're using another format and I make another code snippet.

Upvotes: 0

Related Questions