Delete MySQL Rows Older Than Date

I ran into a problem recently where I needed to delete all rows in a MySQL table that were older than three months. This I was able to accomplish with the following statement:

[code]
DELETE FROM [table “” not found /]
WHERE [column] < DATE_SUB(NOW(), INTERVAL 3 MONTH);
[/code]

Substitute table and column, and a proper date option. The date_sub function subtracts a time period from the date specified. In this statement, NOW() grabs the current date from which INTERVAL 3 MONTHS is subtracted (along with the < operand).

About Benjamin Perove

Ben has been associated with a broad spectrum of technologies starting from an early age, and he's contributed to the success of many businesses and enterprises professionally since 2001. Most of his time is spent building cool stuff. When he's not working, he enjoys reading, playing acoustic guitar, and being with friends. He currently resides in Medellin, Colombia.

  • Pingback: MySQL Tutorials()

  • Steve

    I tried to run this and I get an error. Here is mine
    _______________
    DELETE FROM ‘cl_user’ WHERE ‘user_date_last_login’ < DATE_SUB(NOW(), INTERVAL 3 MONTH);
    _______________

    The error I get is.
    _______________

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''cl_user' WHERE 'user_date_last_login' < DATE_SUB(NOW(), INTERVAL 3 MONTHS)' at line 1

    _______________

    Any Ideas??? I want to use this to delete users who have not logged in for more than 90 days. Let me know thanks so much.

  • ashraf

    dear,

    I want to delete data from a table from specified date to specified date. what will be the command?

  • http://www.unifiedsoftware.co.uk George

    Use the BETWEEN operator in the WHERE clause

  • http://fcab.se Patrik Idén

    Did you resolv this?

    I tried to run this and I get an error. Here is mine
    _______________
    DELETE FROM ‘cl_user’ WHERE ‘user_date_last_login’ < DATE_SUB(NOW(), INTERVAL 3 MONTH);
    _______________

    The error I get is.
    _______________

    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''cl_user' WHERE 'user_date_last_login' < DATE_SUB(NOW(), INTERVAL 3 MONTHS)' at line 1

    _______________

    Any Ideas??? I want to use this to delete users who have not logged in for more than 90 days. Let me know thanks so much.

  • Leandro

    Hey Patrik,
    Try removing the quotes ” from the name of the table and field.. that might help

  • Jaffir

    That was exactly what i was looking for

  • Pingback: Delete information if older than… | GeekFreak()

Got WordPress Security?

You will definitely wish you had downloaded my top 5 recommendations when you're cleaning out malicious Javascript from deep within you WordPress site. Enter your email and get the PDF right now, before it's too late.