CODEWITHSUNDEEP
sqlsqlitesql-deletesqldatetime
user3030327
user3030327

Reputation: 451

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.

Upvotes: 1

Views: 1982

Answers (2)

forpas
forpas

Reputation: 164139

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, '/', '-')

Upvotes: 2

Ayse
Ayse

Reputation: 632

declare @end date = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

DELETE FROM table1 WHERE date_column < @end

Upvotes: 0

Related Questions