I was reviewing some code recently that has been in production for a while and I saw something like this:

set @SomeDate = getdate()

delete from sometable where datecol > @SomeDate – 3

…..lots of work here, several seconds to a minute

Insert Into SomeTable….new rows where DateCol > @SomeDate – 3

The problem is that they’ve left some room for error. That time span between the delete and insert leaves a window to miss records. How many can you miss in a minute? A lot of times none on a system that gets used mainly 9 to 5, and even then you might not miss any if your luck holds. But add more processing so that the window widens, increase the volume, or any other thing that can easily happen, and now you have missed (and maybe lost) records.

The fix is easy, just move the math out of the statements and into the assignment:

set @SomeDate = getdate()  – 3

On a separate note some might argue that you should use DateDiff for this. I like DateDiff, but for this kind of case I’m ambivalent. I think it’s easy enough to understand the intent (though for those of you new to SQL, it’s subtracting 3 days).