Delete all rows with timestamp older than x days
DELETE FROM on_search
WHERE search_date < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 180 DAY))
Delete rows with date older than 30 days with SQL Server query
Use DATEADD in your WHERE clause:
...
WHERE date < DATEADD(day, -30, GETDATE())
You can also use abbreviation d
or dd
instead of day
.
How to delete records from database table older than 30 days with based on date column
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, '/', '-')
sql delete all rows older than 30 days
The following code will delete the records of messages that are older than 30 days
DELETE FROM messages WHERE sentOn < NOW() - INTERVAL 30 DAY;
The NOW()
method in MySQL is used to pick the current date with time. INTERVAL 30 DAY
used for subtracting 30 days from the current date.
After the above query, you can check the current table using the SELECT
statement. Thank you!
How to delete only Top 100 rows which are older than 30 days from current date in C#?
When TOP is used with DELETE, the referenced rows are not arranged in any order and the ORDER BY clause can not be directly specified in this statement. If you need to use TOP to delete rows in a meaningful chronological order, you must use TOP together with an ORDER BY clause in a subselect statement
your sql script should be like this
DELETE from [Logs]
WHERE Id IN
(SELECT TOP(100) Id
FROM Logs
WHERE DATEDIFF(day, DateCreated, GETDATE()) < 30
Order By DateCreated )
Dropping mssql data records for records older than xx days
Your code is deleting everything newer than 3 days old, and should be:
DELETE FROM Results WHERE [Date] < DATEADD(DAY, -3, GETDATE())
Otherwise, this is fine for deleting everything that is older than exactly 72 hours, although as others have said you should definitely be testing either on a copy of the data or at least by using SELECT statement
SELECT * FROM Results WHERE [date] < DATEADD(DAY, -3, GETDATE())
However, if you want to delete everything that happened before three days ago (e.g. It is now Monday, and I want to delete everything that happened before Friday) you would need to eliminate the time aspect.
DELETE FROM Results WHERE CONVERT(DATE, [date]) < DATEADD(DAY, -3, GETDATE())
SQL Server Delete rows when timestamp is older than 1 month
Rounding issues with the month function may give you issues - you'd probably be better off with a DATEADD() instead, e.g.,
DELETE FROM myTable WHERE MyColumn < DATEADD(MONTH, -1, GETDATE())
Related Topics
Mysql Inner Join Select Only One Row from Second Table
How to Get Latest Records for Each Ledger Account Based on Variationid
Laravel Eloquent Mutiple Counts With Different Conditions in One Query
Splitting String into Multiple Rows in Oracle
Way to Find Data of a SQL Table With Same Status for Consecutive 3 Days
Use Current Date as Default Value for a Column
Find Employee Name Who Is Having Highest Salary in Given Month in SQL
How to Convert Bigint (Unix Timestamp) to Datetime in SQL Server
How to Get Total Count Value Each Day Upto 5 Days
How to Have a Default Parameter for a MySQL Stored Procedure
Database Table With Million of Rows
Get the Number of Digits After the Decimal Point of a Float (With or Without Decimal Part)
How to Select Three Table With Same Column Name But Different Values
How to Select Rows With No Matching Entry in Another Table
Select Different Values from Same Column in a Table and Display It Under Different Columns
How to Modify Query to Remove Double-Quotes from All Columns