CODEWITHSUNDEEP
sqlsql-server
StringBuilder
StringBuilder

Reputation: 1629

SQL Server Delete all rows more than 10 minutes old

How can I delete All rows in SQL Server that were created later than 10 minutes. Thanks.

Upvotes: 4

Views: 7765

Answers (4)

el martillo - the hammer
el martillo - the hammer

Reputation: 11

I use this syntax because I find it easy to use:

This does require that there is an column for timestamps - which should be SOP for any production system.

WHERE DATEDIFF(MINUTE, order.credat, CURRENT_TIMESTAMP) < 10

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172548

Assuming that you have a column name Date_column which is storing the timestamp. You may try like this, where mi is the abbreviation for minute:

DELETE FROM Table_name
WHERE Date_column < DATEADD(mi,-10,GETDATE())

Upvotes: 8

Alireza Fattahi
Alireza Fattahi

Reputation: 45553

Add a row to your table which holds the created date modified.

Create a trigger which updates this row

CREATE TRIGGER dbo.trigger_table_update

    ON dbo.MyTable AFTER UPDATE
    AS BEGIN
        UPDATE dbo.MyTable  SET modified = GETDATE()
             FROM INSERTED i
                  WHERE dbo.MyTable.ID = i.ID
    END

Now you can delete 

DELETE FROM MyTable
WHERE modified  < DATEADD(mm,-10,GETDATE())

Please review How to track when a row was created/updated automatically in SQL Server?

Upvotes: 1

Pepe
Pepe

Reputation: 6480

SQL Server has no way to tell at what time the row was created. You will need to have an extra column in your table to log the time. If you do that, your delete statement becomes fairly straight forward.

Upvotes: 1

Related Questions