Database Administrators

Return to Answer

2 added 170 characters in body
source | link
inline side-by-side side-by-side markdown 
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

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

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
1
source | link

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:

+------------------------+--------------------------+--------------------------+
|          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 |
+------------------------+--------------------------+--------------------------+