Need to add [LastModifiedOn] to the SET statement too, so it gets set (to today's date) when the update runs.
SET wh.Due_Date = DATEADD(DAY, 1, wh.Due_Date),
LastModifiedOn = GetDate()
Not sure about the
bit. Sorry, I'm making something straightforward into something horribly complicated, but BeenThereDoneThat and all the business about getting the Tee-shirt too ...
For a scheduled task it can run late - maybe a few milliseconds, maybe even a second or two. Then if tomorrow's process runs at the normal time then 24 hours (exactly) won't have elapsed yet.
I think I would want it to only run once per "calendar" day
AND LastModifiedOn < DATEADD(Day, DATEDIFF(Day, 0, GetDate()), 0)
which will be midnight last night.
I wonder if there is an easier / more efficient way to do that in current SQL versions? Perhaps CAST as just as efficient? Certainly easlier to read!
AND LastModifiedOn < CAST(GetDate() AS DATE)
a system variable that delivered Now Date, without Now Time, would be handy ...