This isn't really all that complex, and we can use code that doesn't require any specific date setting(s). I used a CROSS APPLY just to get the date calc out of the main query.
select *, dbo.NumDOWBetween
(
DOW,
StartDate,
EndDate) AS Function_Result
,CASE WHEN startdate_DOW > enddate THEN 0 ELSE
1 + DATEDIFF(DAY, startdate_DOW, EndDate) / 7 END AS Query_Result
from ##TempMyDates
cross apply (
select DATEADD(DAY, -DATEDIFF(DAY, CASE DOW WHEN 'Mon' THEN 0 WHEN 'Tue' THEN 1 WHEN 'Wed' THEN 2
WHEN 'Thu' THEN 3 WHEN 'Fri' THEN 4 WHEN 'Sat' THEN 5 ELSE 6 END,
DATEADD(DAY, 6, StartDate))%7, DATEADD(DAY, 6, StartDate)) as startdate_DOW
) as find_first_dow_on_or_past_startdate
Edit: Put SQL code in a code block. Hopefully the names used explain the code, but if you need additional clarification, naturally please just say so.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.