2

To Get a list of weeks, i simply run this (found online):

SET DATEFIRST 7
;with DateCTE   as
(
    select cast('01/01/2017' as datetime) DateValue
    union all
    select DateValue + 1
    from    DateCTE  
    where   DateValue + 1 < '1/1/2018'
)


Select
    cast(CAST(DateValue AS CHAR(11)) AS DateTime) AS [FullDate],
    DATEPART(wk, DateValue) AS WeekOfYear,
    DATEPART(yy, DateValue) AS CalendarYear 
from   DateCTE
OPTION (MAXRECURSION 0)

enter image description here

Obviously if I SET DATEFIRST = 1, then it gets different results.

enter image description here

I need to compare the days in one table, without using table variables. This is what I'd like to achieve: enter image description here

Thus I'd like to see the same 365 days that will be available in both CTE's,

I want to compare the WeekOfYear based on different DateFirsts.

I do not know how to do this effectively. I can only get it to work when I

  1. Create table variable 1 and run the CTE for DateFirst7
  2. Create table variable 2 and run the CTE for DateFirst1

This is inefficient and I am looking for a better way to do this.

There are a few things working against this 1. I dont know how to set the DateFirst inside the query. I only know how to set it before the cte. 2. I would like to use nested CTE's for this, but OPTION (MAXRECURSION 0) does not work well with nested CTE's

Is this possible - using CTE's to get required results?

0

3 Answers 3

4

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;
0
3

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

The joys of the edge case. Max's solution works fine for 2017, because 2017 started on a Sunday. If you expand the date range, you'll see that years that start on a Sunday are fine. However, it the year starts on any other day of the week, the partial week before that first Sunday will always show as Week 0. Some businesses might want that to be week 0 (since it's a partial week), or part of the previous year, or whatever - however, partial weeks for the Monday dates are week 1, not week 0, so we have a discrepancy.

The simplest way I saw to solve it was to check if the year started on Sunday. If it does, then use Max's original code for WeekOfYearStartingSunday; if not, just add one to what he had:

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
        CROSS JOIN Nums n4 --10000 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, DATEADD(YEAR, DATEPART(YEAR, d.date) - 1900, 0)) = 7
                                      -- Jan 1 is a Sunday
                                      THEN CASE WHEN DATEPART(WEEKDAY, d.Date) = 7
                                                THEN DATEPART(WEEK, d.Date) 
                                                ELSE DATEPART(WEEK, d.Date) - 1 
                                           END

                                      -- Jan 1 is NOT a Sunday
                                      ELSE CASE WHEN DATEPART(WEEKDAY, d.Date) = 7
                                                THEN DATEPART(WEEK, d.Date) + 1
                                                ELSE DATEPART(WEEK, d.Date)
                                           END
                                 END
FROM Dates d
-- WHERE added to just show first and last 9 days of each year; remove to see all dates
WHERE (DATEPART(MONTH, d.date) = 12 AND DATEPART(DAY, d.date) > 22)
   OR (DATEPART(MONTH, d.date) = 1 AND DATEPART(DAY, d.date) < 10)
ORDER BY d.Date;
GO

Here are the first and last days of the first six years (years starting on every day but Thursday - that goes out a bit farther, due to leap years):

+-------------------------+--------------------------+--------------------------+
| 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 |
| Saturday Dec 23, 2017   |                       52 |                       51 |
| Sunday Dec 24, 2017     |                       52 |                       52 |
| Monday Dec 25, 2017     |                       53 |                       52 |
| Tuesday Dec 26, 2017    |                       53 |                       52 |
| Wednesday Dec 27, 2017  |                       53 |                       52 |
| Thursday Dec 28, 2017   |                       53 |                       52 |
| Friday Dec 29, 2017     |                       53 |                       52 |
| Saturday Dec 30, 2017   |                       53 |                       52 |
| Sunday Dec 31, 2017     |                       53 |                       53 |
| Monday Jan 01, 2018     |                        1 |                        1 |
| Tuesday Jan 02, 2018    |                        1 |                        1 |
| Wednesday Jan 03, 2018  |                        1 |                        1 |
| Thursday Jan 04, 2018   |                        1 |                        1 |
| Friday Jan 05, 2018     |                        1 |                        1 |
| Saturday Jan 06, 2018   |                        1 |                        1 |
| Sunday Jan 07, 2018     |                        1 |                        2 |
| Monday Jan 08, 2018     |                        2 |                        2 |
| Tuesday Jan 09, 2018    |                        2 |                        2 |
| Sunday Dec 23, 2018     |                       51 |                       52 |
| Monday Dec 24, 2018     |                       52 |                       52 |
| Tuesday Dec 25, 2018    |                       52 |                       52 |
| Wednesday Dec 26, 2018  |                       52 |                       52 |
| Thursday Dec 27, 2018   |                       52 |                       52 |
| Friday Dec 28, 2018     |                       52 |                       52 |
| Saturday Dec 29, 2018   |                       52 |                       52 |
| Sunday Dec 30, 2018     |                       52 |                       53 |
| Monday Dec 31, 2018     |                       53 |                       53 |
| Tuesday Jan 01, 2019    |                        1 |                        1 |
| Wednesday Jan 02, 2019  |                        1 |                        1 |
| Thursday Jan 03, 2019   |                        1 |                        1 |
| Friday Jan 04, 2019     |                        1 |                        1 |
| Saturday Jan 05, 2019   |                        1 |                        1 |
| Sunday Jan 06, 2019     |                        1 |                        2 |
| Monday Jan 07, 2019     |                        2 |                        2 |
| Tuesday Jan 08, 2019    |                        2 |                        2 |
| Wednesday Jan 09, 2019  |                        2 |                        2 |
| Monday Dec 23, 2019     |                       52 |                       52 |
| Tuesday Dec 24, 2019    |                       52 |                       52 |
| Wednesday Dec 25, 2019  |                       52 |                       52 |
| Thursday Dec 26, 2019   |                       52 |                       52 |
| Friday Dec 27, 2019     |                       52 |                       52 |
| Saturday Dec 28, 2019   |                       52 |                       52 |
| Sunday Dec 29, 2019     |                       52 |                       53 |
| Monday Dec 30, 2019     |                       53 |                       53 |
| Tuesday Dec 31, 2019    |                       53 |                       53 |
| Wednesday Jan 01, 2020  |                        1 |                        1 |
| Thursday Jan 02, 2020   |                        1 |                        1 |
| Friday Jan 03, 2020     |                        1 |                        1 |
| Saturday Jan 04, 2020   |                        1 |                        1 |
| Sunday Jan 05, 2020     |                        1 |                        2 |
| Monday Jan 06, 2020     |                        2 |                        2 |
| Tuesday Jan 07, 2020    |                        2 |                        2 |
| Wednesday Jan 08, 2020  |                        2 |                        2 |
| Thursday Jan 09, 2020   |                        2 |                        2 |
| Wednesday Dec 23, 2020  |                       52 |                       52 |
| Thursday Dec 24, 2020   |                       52 |                       52 |
| Friday Dec 25, 2020     |                       52 |                       52 |
| Saturday Dec 26, 2020   |                       52 |                       52 |
| Sunday Dec 27, 2020     |                       52 |                       53 |
| Monday Dec 28, 2020     |                       53 |                       53 |
| Tuesday Dec 29, 2020    |                       53 |                       53 |
| Wednesday Dec 30, 2020  |                       53 |                       53 |
| Thursday Dec 31, 2020   |                       53 |                       53 |
| Friday Jan 01, 2021     |                        1 |                        1 |
| Saturday Jan 02, 2021   |                        1 |                        1 |
| Sunday Jan 03, 2021     |                        1 |                        2 |
| Monday Jan 04, 2021     |                        2 |                        2 |
| Tuesday Jan 05, 2021    |                        2 |                        2 |
| Wednesday Jan 06, 2021  |                        2 |                        2 |
| Thursday Jan 07, 2021   |                        2 |                        2 |
| Friday Jan 08, 2021     |                        2 |                        2 |
| Saturday Jan 09, 2021   |                        2 |                        2 |
| Thursday Dec 23, 2021   |                       52 |                       52 |
| Friday Dec 24, 2021     |                       52 |                       52 |
| Saturday Dec 25, 2021   |                       52 |                       52 |
| Sunday Dec 26, 2021     |                       52 |                       53 |
| Monday Dec 27, 2021     |                       53 |                       53 |
| Tuesday Dec 28, 2021    |                       53 |                       53 |
| Wednesday Dec 29, 2021  |                       53 |                       53 |
| Thursday Dec 30, 2021   |                       53 |                       53 |
| Friday Dec 31, 2021     |                       53 |                       53 |
| Saturday Jan 01, 2022   |                        1 |                        1 |
| Sunday Jan 02, 2022     |                        1 |                        2 |
| Monday Jan 03, 2022     |                        2 |                        2 |
| Tuesday Jan 04, 2022    |                        2 |                        2 |
| Wednesday Jan 05, 2022  |                        2 |                        2 |
| Thursday Jan 06, 2022   |                        2 |                        2 |
| Friday Jan 07, 2022     |                        2 |                        2 |
| Saturday Jan 08, 2022   |                        2 |                        2 |
| Sunday Jan 09, 2022     |                        2 |                        3 |
| Friday Dec 23, 2022     |                       52 |                       52 |
| Saturday Dec 24, 2022   |                       52 |                       52 |
| Sunday Dec 25, 2022     |                       52 |                       53 |
| Monday Dec 26, 2022     |                       53 |                       53 |
| Tuesday Dec 27, 2022    |                       53 |                       53 |
| Wednesday Dec 28, 2022  |                       53 |                       53 |
| Thursday Dec 29, 2022   |                       53 |                       53 |
| Friday Dec 30, 2022     |                       53 |                       53 |
| Saturday Dec 31, 2022   |                       53 |                       53 |
+-------------------------+--------------------------+--------------------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.