You can simulate modifying the `DATEFIRST` setting since it's a well known formula where you are simply changing the first day of the week. 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 = CASE WHEN DATEPART(WEEKDAY, d.Date) = 7 THEN DATEPART(WEEK, d.Date) ELSE DATEPART(WEEK, d.Date) - 1 END FROM Dates d ORDER BY d.Date; GO The first couple of weeks from the results: <pre>+------------------------+--------------------------+--------------------------+ | Date | WeekOfYearStartingMonday | WeekOfYearStartingSunday | +------------------------+--------------------------+--------------------------+ | Sunday Jan 01, 2017 | 1 | 1 | | Monday Jan 02, 2017 | 2 | 1 | | Tuesday Jan 03, 2017 | 2 | 1 | | Wednesday Jan 04, 2017 | 2 | 1 | | Thursday Jan 05, 2017 | 2 | 1 | | Friday Jan 06, 2017 | 2 | 1 | | Saturday Jan 07, 2017 | 2 | 1 | | Sunday Jan 08, 2017 | 2 | 2 | | Monday Jan 09, 2017 | 3 | 2 | | Tuesday Jan 10, 2017 | 3 | 2 | | Wednesday Jan 11, 2017 | 3 | 2 | | Thursday Jan 12, 2017 | 3 | 2 | | Friday Jan 13, 2017 | 3 | 2 | | Saturday Jan 14, 2017 | 3 | 2 | | Sunday Jan 15, 2017 | 3 | 3 | | Monday Jan 16, 2017 | 4 | 3 | | Tuesday Jan 17, 2017 | 4 | 3 | | Wednesday Jan 18, 2017 | 4 | 3 | | Thursday Jan 19, 2017 | 4 | 3 | | Friday Jan 20, 2017 | 4 | 3 | | Saturday Jan 21, 2017 | 4 | 3 | | Sunday Jan 22, 2017 | 4 | 4 | | Monday Jan 23, 2017 | 5 | 4 | +------------------------+--------------------------+--------------------------+</pre>