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?

  • 1
    I'm not sure what you're asking. Do you just want a list of dates in a given year, along with which week of the year the date falls in, for any given DATEFIRST? – Philᵀᴹ Mar 24 '17 at 14:19
  • I have re-read my own question and understand your comment and consequently updated the question to give more clarity and explain myself better. I am trying to see the common list of days in the year, and in which Week that day falls, based on DateFirst. – Peter PitLock Mar 24 '17 at 14:58
  • Ah right, given 2 DATEFIRSTs, which dates in a given year have the same week number? – Philᵀᴹ Mar 24 '17 at 15:04
  • That would put me on a good path yes. Different clients have different DateFirst values. This table would match up where they align and where they are out. Along this I've picked up that is extremely difficult to nest a CTE and use OPTION MAXRECURSION(0). I also picked up it is near impossible to update (not insert) a table variable with a value from a CTE. – Peter PitLock Mar 24 '17 at 15:10
  • 1
    When you say that the code is inefficient what does that mean? It takes a minute to run? Why not just do the calculation once and save the results to a permanent table? Then it doesn't matter if it takes a minute to run the first time. That seems like a much better option than always doing the week calculations on the fly? – Joe Obbish Mar 24 '17 at 15:50
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;
  • This is amazing, thank you Andriy and @MaxVernon and RDFozz – Peter PitLock Mar 27 '17 at 11:20
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 |
+------------------------+--------------------------+--------------------------+
  • Is there a way to make this method work for any year? When I try it for 2018 I see week 0s. – Forrest Mar 24 '17 at 19:08
  • @Forrest - N'2016-12-31T00:00:00') in the code above indicates this starts at January 1st, 2017. Switch that to any year of your choice, or add more `CROSS JOINs' – Max Vernon Mar 24 '17 at 19:48
  • 1
    Actually, @Forrest is right. Explanation wouldn't fit well in comments, so I posted it as a new answer. However, my answer is entirely based on yours. – RDFozz Mar 24 '17 at 22:08
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, privacy policy and cookie policy

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