Delete Rows With Date Older Than 30 Days With SQL Server Query

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



Leave a reply



Submit