Sql-server – Compare each day of year with DATEFIRST = 1 and DATEFIRST = 7

ctesql server

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?

Best Answer

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;
Related Question