Odd behavior of between operator when updating
The SQL statement you've listed, run by itself, would not alter the FLAG column.

I've put together a SQLFiddle that shows what that statement does on its own, and you'll see that FLAG isn't changed by it.

This is why so many have assumed there's a trigger, or another statement, involved in the process.

You've said that there's no trigger on SOME_TABLE. If so, then some other statement is making the change.

The best way to find what that statement is would be to use SQL Profiler (or extended events) to track what's being executed when your job runs. If some other statement is being triggered by the relevant job, this should show you what that is. If you search this site, you'll find information on these options.

If you need to prove to yourself whether or not the UPDATE statement in and of itself is causing the change to FLAG, I'd recommend the following:

During a maintenance window:


Begin a transaction
Run the UPDATE statement inside that transaction
Check the results of the statement on SOME_TABLE (again from within the transaction)
Roll back the transaction.


If there's no transaction, and that SQL Statement (not the whole job, just that statement) does change the value of FLAG, then either this is tied to a bug in SQL Server (not impossible, but somewhat unlikely), or there's something seriously wrong with your SQL Server installation. Or, of course, there really is a trigger - I'm assuming not, based on what you've said, but that's a much more likely solution than a bug or a messed up SQL Server instance.