Compare each day of year with DATEFIRST = 1 and DATEFIRST = 7
I started with an attempt at fixing Max Vernon's answer so that it would work for any year, but ended up with two different approaches.

Approach 1. Being clever

In this method I am calculating the Sunday-based week number directly from the offset from the year's first Sunday-based week's Sunday.

A year's first Sunday-based week's Sunday can be obtained like this:

SET DATEFIRST 1;
SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @FirstDayOfYear) % 7), @FirstDayOfYear);


You are taking the week day of January 1 as a number of days and subtracting it from January 1, unless that date falls on Sunday, in which case you are subtracting nothing (that is what the … % 7 is for). For Monday-based week numbers, the result of the above formula will always be a Sunday, and in this case it will be the Sunday of the first Sunday-based week of the year.

Before going on to calculating Sunday-based week numbers, here is another formula for turning any date into January 1 of the same year:

SELECT DATEADD(DAY, 1 - DATEPART(DAYOFYEAR, @Date), @Date);


Basically, take the date and subtract its DAYOFYEAR value and add one day.

Now here is a query that is using Max Vernon's set of CTEs, the above two formulae and another one to get the actual week number:

SET DATEFIRST 1;
WITH Nums AS (
    SELECT v.Num 
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(Num)
)
, Dates AS
(
    SELECT Date = DATEADD(DAY
                , ROW_NUMBER() OVER (ORDER BY n1.Num, n2.Num, n2.Num)
                , N'2016-12-31T00:00:00')
    FROM Nums n1 --10 rows
        CROSS JOIN Nums n2 --100 rows
        CROSS JOIN Nums n3 --1000 rows, add more cross joins for more rows
)
SELECT [Date] = FORMAT(d.Date, 'dddd MMM dd, yyyy')
    , WeekOfYearStartingMonday = DATEPART(WEEK, d.Date)
    , WeekOfYearStartingSunday = DATEDIFF(DAY, y.Sunday1, d.Date) / 7 + 1
FROM Dates AS d
CROSS APPLY (
    SELECT DATEADD(DAY, 1 - DATEPART(DAYOFYEAR, d.Date), d.Date)
) AS x (January1)
CROSS APPLY (
    SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, x.January1) % 7), x.January1)
) AS y (Sunday1)
ORDER BY d.Date;


Approach 2. Using system loophole

This method is completely different in that it relies on a built-in peculiarity of the DATEDIFF function.

As you probably know, setting DATEFIRST affects the results of such functions as DATEPART(WEEK) and DATEPART(WEEKDAY). Unlike them, DATEDIFF(WEEK) does not respect the DATEFIRST setting. It always calculates the difference as if DATEFIRST were set to 7, that is always treating weeks as Sunday-based.

Knowing that, you can calculate the difference in weeks between January 1 and any date of the same year, add 1 and use that as the Sunday-based week number:

SET DATEFIRST 1;
WITH Nums AS (
    SELECT v.Num 
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(Num)
)
, Dates AS
(
    SELECT Date = DATEADD(DAY
                , ROW_NUMBER() OVER (ORDER BY n1.Num, n2.Num, n2.Num)
                , N'2016-12-31T00:00:00')
    FROM Nums n1 --10 rows
        CROSS JOIN Nums n2 --100 rows
        CROSS JOIN Nums n3 --1000 rows, add more cross joins for more rows
)
SELECT [Date] = FORMAT(d.Date, 'dddd MMM dd, yyyy')
    , WeekOfYearStartingMonday = DATEPART(WEEK, d.Date)
    , WeekOfYearStartingSunday = DATEDIFF(WEEK, x.January1, d.Date) + 1
FROM Dates AS d
CROSS APPLY (
    SELECT DATEADD(DAY, 1 - DATEPART(DAYOFYEAR, d.Date), d.Date)
) AS x (January1)
ORDER BY d.Date;