CODEWITHSUNDEEP
sqldate
Retrocoder
Retrocoder

Reputation: 4713

How to delete records base on a date and offset

I need to delete records in a table if the current date is greater than the record creation date + a preset number of days (defined by @numberOfDays). I am using the following SQL statement but am not sure if it’s very efficient. Is there a better way?

I am using MS SQL 2008 server.

DELETE
FROM deviceManager.Test2
WHERE DATEADD(day, @numberOfDays, deviceManager.Test2.GeneratedAt_UTC) < SYSDATETIMEOFFSET()

@numberOfDays is an int with a value of 10

Upvotes: 2

Views: 286

Answers (2)

Brian Frantz
Brian Frantz

Reputation: 752

Probably better to adjust the current date instead of every record in the table. Change the logic to "record creation date is before current datetime - @numberOfDays."

This is more efficient - one calculation instead of many.

Upvotes: 2

Francis Upton IV
Francis Upton IV

Reputation: 19443

Seems OK to me, I can't think of a better way off the top of my head.

Upvotes: 0

Related Questions