Search code examples
sqlsqlitesql-deletesqldatetime

How to delete records from database table older than 30 days with based on date column

This is the code in python that i am using below. But its not working and not showing any error.

delquery = "DELETE FROM table1 WHERE date_column < date ('now', '-30 day')"        
conn.execute(delquery)

I am using SQLite Database.

Solution

  • If the format is yyyy/MM/dd you must change it to yyyy-MM-dd, because this is the only valid comparable format for dates in SQLite:

    DELETE FROM table1 WHERE REPLACE(date_column, '/', '-') < date('now', '-30 day')

    Or even better update the table, so date_column is in the proper format and you don't need the function REPLACE() every time you want to compare dates:

    UPDATE table1
SET date_column = REPLACE(date_column, '/', '-')