Note: My original code was not helpful (wrong!), the corrected version is shown below.
I was reviewing some code recently that has been in production for a while and I saw something like this:
delete from sometable where datecol > getdate() – 3
…..lots of work here, several seconds to a minute
Insert Into SomeTable….new rows where DateCol > Getdate() – 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).
SQLAndy 
Maybe I”m missing something but the value of @SomeDate shouldn”t change once it”s been set to getdate(). If the getdate() function is in the two statements then that would be a problem but this will return the same value both times:
declare @SomeDate datetime
set @SomeDate = GETDATE()
select @SomeDate-3
waitfor delay ”00:01”
select @SomeDate-3
LikeLike
Andy, wouldn”t @SomeDate – 3 be equal in both places it is called since from this code it doesn”t look like @SomeDate changes? Just as if you set it to getdate() – 3 once? Unless they used getdate() – 3 where you used @SomeDate – 3, I don”t see the issue.
LikeLike
Sorry guys, mixed problem and solution code when I was trying to convert it to a generic (non-work) example. They were using getdate() inline, not assigning to a variable as I incorrectly showed. Thanks for the note, sucks to get the example wrong.
LikeLike