SQL delete all rows with date older than x days
I have a table like below and I am trying delete all rows with createdDate older than 10 days form current date, like last 3 streams to be deleted form table
ID Name createdDate(string) 76 Stream1 2018-10-18T00:00:00 70 Stream2 2018-10-17T00:00:00 50 Stream3 2018-10-03T00:00:00 32 Stream4 2018-09-22T00:00:00 21 Stream5 2018-09-21T00:00:00
I tried below queried but didn't work, can some one help
DELETE FROM myTable WHERE TO_DATE(createdDate) < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 DAY)) DELETE FROM myTable WHERE TO_DATE(createdDate) < NOW()- INTERVAL 10 DAY;
For performance, to allow MySQL to use a range scan (using an index with
createdDate as the leading column), I would reference the bare column in the
I would do any required manipulation on the literal side of the comparison, to come up with a string that we can compare the bare column to.
I'd write a SELECT statement first, to verify that it's working (before we run a DELETE and find out that it deletes more than we expected)
We can test expressions in a simple SELECT statement, e.g.
SELECT DATE_FORMAT( NOW() + INTERVAL -10 DAY , '%Y-%m-%dT00:00:00') AS dt
Then we can use that expression in a WHERE clause
SELECT t.* FROM myTable t WHERE t.createddate < DATE_FORMAT( NOW() + INTERVAL -10 DAY , '%Y-%m-%dT00:00:00') ORDER BY t.createddate DESC
This expression is using midnight
'00:00:00' as the time component; there are other expressions that would achieve an equivalent result.
WHERE t.createddate < DATE_FORMAT( DATE(NOW()) + INTERVAL -10 DAY , '%Y-%m-%dT%T')
We really need to narrow down and be specific what is meant by "older than 10 days than current date".
Once we are sure that the query is returning the rows we want to delete, I would convert this into a DELETE statement by replacing the SELECT keyword, and omitting the ORDER BY
SELECT t.* FROM myTable t WHERE t.createddate < DATE_FORMAT( NOW() + INTERVAL -10 DAY , '%Y-%m-%dT00:00:00')
DELETE FROM on_search WHERE search_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY)). To delete records from a table that have a datetime value in Date_column older than 12 hours: USE Database_name; DELETE FROM Table_name WHERE Date_column . DATEADD(hh,-12,GETDATE()) To delete records from a table that have a datetime value in Date_column older than 15 minutes: USE Database_name; DELETE FROM Table_name WHERE Date_column . DATEADD(mi,-15,GETDATE())
I don't think
to_date() is a function in MySQL. Just use
DELETE FROM myTable WHERE DATE(createdDate) < NOW() - INTERVAL 10 DAY;
To delete all rows older than 30 days, you need to use the DELETE with INTERVAL. Use < now() i.e. less than operator to get all the records before the current date. To delete all rows older than 5 days, you can use the following syntax −delete from yourTableName where datediff(now(), yourTableName.yourDateColumnName)
i think date conversion need in both side of comparison
DELETE FROM myTable WHERE DATE(createdDate) < date(NOW()- INTERVAL 10 DAY);
delete from yourTableName where datediff(now(), yourTableName.yourDateColumnName) > 5;. Note − Let's say the current date is 2019-03-10. sql delete statement where date is greater than 30 days. Ask Question. Asked 9 years, 2 months ago. Active 2 months ago. Viewed 160k times. 51. I wish to find a SQL statement that when executed deletes fields that are older then 30 days.
SQL Server « «. » » Insert multiple records into a SQL Server table using one INSERT statement in SQL Server. To delete records from a table that have a datetime value in Date_column older than 30 days use this query: First run above statement and make sure it returns the correct date ie last date before 2 month period. once its expected value go ahead and do the delete using below query. DELETE FROM Table WHERE TimeStamp < DATEADD(mm,DATEDIFF(mm,0,GETDATE())-2,0)
There is no function
TO_DATE in MySQL. You can try this query instead:
DELETE FROM myTable WHERE DATE(createDate) < DATE_SUB(NOW(), INTERVAL 10 DAY);
$timepost = date("l dS of F Y H:i:s"); $sql = "DELETE FROM plog WHERE logtime < DATE_SUB(\"$timepost\", INTERVAL 2 DAY)"; all my logtimes Last Modified: 2010-04-21. I am looking to write an SQL query that will delete all records in a table older than 7 days. Here's the initial gist: delete from dnn_EventLog where LogCreateDate (is older than 7 days) Of course, I will set this up to run nightly during off peak hours. The LogCreateDate field is a standard datetime field.
of removing drafts I would like to select all posts older than 7 days then delete My dates are stored in unix time so I'm not sure if Date_Sub won't work with i've seen many people write queries to retrieve data based on “the last X days”… Hi , I have a requirement , where i have to delete top 1000 rows from all tables in database , if tables are older than 2 weeks. I need to accomplish this with a stored proc.
I ran into a problem recently where I needed to delete all rows in a MySQL table that were older than three months. ToÂ delete MySQL/MariaDB 1. Re: Deleting Rows older than 60 days without any date Column. sol.beach Dec 4, 2015 6:54 PM ( in response to 975743) I am looking for a query or script to delete rows older than 60 days from a table without DATE column. I have to set this for every week as autojob/crontab job.. I am thinking about rownum which may help me.
I have a requirement , where i have to delete top 1000 rows from all tables in database , if tables are older than 2 weeks. I need to accomplish this with a as DATE))) select @SQL = (select ' DELETE TOP (1000) FROM ' + Delete rows based on date I need to delete any rows that contain a date that is older than 15 days from the current date. Header in M4, data string starts m5 down.