Nothing to do with full-blown "novices".  I would expect people with reasonable experience in SQL Server to know what 0 is, it's the default "base date" in SQL Server.  If...

I learnt this from a wise old network manager who told me that you catch more flies with honey...

Along with the excellent suggestions above, also look very carefully at the query plans to see if you're hitting a "tipping point" query.  That is, one query that returns, for...

There's at least one other thread besides this one where someone used it and got the wrong date, because they added a month after the calc with -1 in it.  For...

People often learn via patterns.

If I see a pattern like "DATEADD(<time_period>, DATEDIFF(..." in consistent code, I know already what the result of the main computation will be.  I don't have...

The standard pattern for getting the first day of the month is:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)

Stick to that pattern, don't corrupt it with -1 tricks!  That just makes it more...

Apparently, the morning coffee hasn't kicked in for me yet today... what would the CREATE...

Or re-create the index so that it allows multiple NULLs but no other dups.

select s.c1,s.c2,s.c3,  COUNT(*)*IIF(m.c1 IS NULL,0,1) cnt
  from stg_tbl s
  left join main_tbl m 
         on...

But my god MSDTC can play havoc with you when you do remote updates.

Yes, quite true.  Thus, when you can, with relatively low amounts of data, don't do remote updates. ...

Replication has far more headaches than a simple linked server.  I'm not against replication when it's really needed, of course, but I don't see that need here.  I, too, would...

Ok, for a fellow DBA.  I actually "stole" this from a Paul White article.  To be honest, I wasn't aware that the cursor setting prevented allocation scan reads until I...

You can get phantom reads and repeat reads using the default iso level of READ COMMITTED.  The only thing NOLOCK adds is dirty reads.  And you can greatly reduce the...

I do use SET DATEFORMAT as needed.  I agree, it's much easier to do that than to try to rewrite a script.  Then reset it asap to its original value...

I'd strongly advise against messing with DATEFIRST setting.  The code below works under any/all DATEFIRST settings.

declare @date_to_calc_week_of date
set @date_to_calc_week_of = '20191028'

;with cte_date_calcs as (
   ...