I would go at this from another direction:

Dynamically generate an UPDATE statement that does all the REPLACEs (or one UPDATE for each set of rows if there are variations for...

It's really ugly, but you could drop all open ms down to the next-lower ms break and bump up all close ms down to the next-higher ms break. [No...

Yes, SARGable was the wrong term for me to use.

But if there is a covering index which includes both columns, and the date1_col is in the first / next position...

Here's another suggestion that should simplify your task considerably.

Instead of coding the actual table name in the prod queries, use a view name. This should allow you to get...

Not that I'm aware of, if you're counting down to that level.

The key point is to allow one of the columns to be non-manipulated in any way so that it...

Absolutely right.

If this is a commonly used query, I would probably "bite the bullet" and make sure there was an effective (i.e. time-saving) covering index and that SQL was actually...

Well stated Lowell.

Btw, I think that calc returns a Monday since 01/01/1900 happended to be a Monday 🙂 .

WHERE date_col1 >= DATEADD(DAY, DATEDIFF(DAY, 0, date_col2), 0)

AND date_col1 < DATEADD(DAY, DATEDIFF(DAY, 0, date_col2) + 1, 0)

Or:

WHERE date_col1 BETWEEN DATEADD(DAY, DATEDIFF(DAY, 0, date_col2), 0) AND DATEADD(MS, -3, DATEADD(DAY, DATEDIFF(DAY, 0,...

However, as you are working in a larger environment, the optimizer may even choose to use a non-covering index with less than fantastic selectivity just because the cost of the...

From what I've read, the DATEDIFF() method has proven to be more efficient.

Overall, for SQL to use any index, it has to highly selective or a covering index: I...

So, even if the original requirements include the following:

1) use one statement only, without subquery(ies)

2) this is not a design q; it is not intended to be a design q;...

For SQL Server 2008, you can use MERGE.

Prior to SQL '08, don't think you can do it in one statement.

The job could even run every day, since it would just exit quickly if nothing needed done.

Yes, if partitioning is available, I think that is your best solution.

If not, maybe schedule a job that could check to see if a table create/'switch' needed done and, if...

You may be having the problem because it's doing purely integer division 1010/4000 = 0 in int division (decimals ignored).

You might try making the "100" be "100.0" or explicitly forcing...