Need list of weeknumbers between two dates

Question

I need a resultset of weeknumers, year and startdate of all weeks between two dates. I need it to match other search results to the weeks. Since the report will span just over a year, I need it to match the calendars.

We're in Europe, so weeks start on MONDAY. I use SQL Server via a JDBC connection. I cannot use the calander table.

I've come across various solutions, but none does just what I need it to. This is the kind of list I need, but somehow the results are not correct. I can't find my mistake:

WITH mycte AS
(
 SELECT DATEADD(ww, DATEDIFF(ww,0,CAST('2010-12-01' AS DATETIME)), 0) DateValue
 UNION ALL
 SELECT  DateValue + 7
 FROM    mycte   
 WHERE   DateValue + 7 < '2016-12-31'
)

SELECT  DATEPART(wk, DateValue) as week, DATEPART(year, DateValue) as year, DateValue
FROM    mycte
OPTION (MAXRECURSION 0);

I used --SET DATEFIRST 1; to make sure weeks start on monday.

The result looks like:

week        year        DateValue                 
----------- ----------- ------------------------- 
49          2010        2010-11-29 00:00:00.0     
50          2010        2010-12-06 00:00:00.0     
51          2010        2010-12-13 00:00:00.0     
52          2010        2010-12-20 00:00:00.0     
53          2010        2010-12-27 00:00:00.0     
2           2011        2011-01-03 00:00:00.0     
3           2011        2011-01-10 00:00:00.0     
4           2011        2011-01-17 00:00:00.0     
5           2011        2011-01-24 00:00:00.0     
6           2011        2011-01-31 00:00:00.0

The problem is obvious. 2010 hasn't 53 weeks, and week 1 is gone. This hapens for other years as well. Only 2015 has 53 weeks.

(note: in iso weeks (Europe) there only 52 weeks in 2010, see wiki: https://en.wikipedia.org/wiki/ISO_week_date)

The following 71 years in a 400-year cycle (add 2000 for current years) have 53 weeks (leap years, with February 29, are emphasized), years not listed have 52 weeks: 004, 009, 015, 020, 026, 032, 037, 043, 048, 054, 060, 065, 071, 076, 082, 088, 093, 099,105, 111, 116, 122, 128, 133, 139, 144, 150, 156, 161, 167, 172, 178, 184, 189, 195,201, 207, 212, 218, 224, 229, 235, 240, 246, 252, 257, 263, 268, 274, 280, 285, 291, 296,303, 308, 314, 320, 325, 331, 336, 342, 348, 353, 359, 364, 370, 376, 381, 387, 392, 398.

The dates are correct though. 2012-12-27 is a monday and so is 2011-01-03. But in Europe we always have a full week 1 (so there is always a monday with weeknumber 1)

Any ideas what hapend to week 1 or why there are so many years with 53 (which is wrong)?

Answer 1

SQL Server is using standard week numbers, which match with Outlook (and while 53 sounds odd it is valid).

Of course you could always create your own custom week numbers. All you need to do is to pick a starting Monday and calculate the weeknumber you would assign. Your CTE can then increment its own week count, which resets to 1 each time the year changes. But even this will return week 53 (Dec 31st 2012 was a Monday, making it the 53rd week of that year, even though the rest of the week feel in 2013).

Worth mentioning; your week numbers are unlikely to match those from other systems/processes. This could cause you problems further down the line.

SET DATEFIRST 1;

WITH [Week] AS
    (
            SELECT
                CAST('2010-11-29' AS DATE)      AS [Date],
                48                              AS WeekNumber

        UNION ALL

            SELECT
                DATEADD(DAY, 7, [Date]) AS [Date],
                CASE
                    -- Reset the week number count when the year changes.
                    WHEN YEAR([Date]) <> YEAR(DATEADD(DAY, 7, [Date])) THEN 1
                    ELSE WeekNumber + 1             
                END AS WeekNumber
            FROM
                [Week]
            WHERE
                [Date] < GETDATE()
    )
SELECT
    *
FROM
    [Week]
OPTION
    (MAXRECURSION 0)
;

Answer 2

The reason you're seeing 53 weeks in 2010 is simply because, there are 53 weeks in 2010.

Let's take a closer look at how the weeks break down in that year:

Declare @FromDate   Date = '2010-01-01',
        @ToDate     Date = '2011-01-03'

;With Date (Date) As
(
    Select  @FromDate Union All
    Select  DateAdd(Day, 1, Date)
    From    Date
    Where   Date < @ToDate
)
Select  Date, DatePart(Week, Date) WeekNo, DateName(WeekDay, Date) WeekDay
From    Date
Option  (MaxRecursion 0)

SQL Fiddle

Here's how the beginning of the year is:

Date       WeekNo      WeekDay
---------- ----------- ------------------------------
2010-01-01 1           Friday
2010-01-02 1           Saturday
2010-01-03 2           Sunday
2010-01-04 2           Monday
2010-01-05 2           Tuesday
2010-01-06 2           Wednesday
2010-01-07 2           Thursday
2010-01-08 2           Friday
2010-01-09 2           Saturday
2010-01-10 3           Sunday

Since the year begins in the middle of a week, there are only two days for Week 1. This causes the year to have 53 total weeks.

Now, to answer your question for why you don't see a Week 1 value for 2011, let's look at how that year ends:

Date       WeekNo      WeekDay
---------- ----------- ------------------------------
2010-12-26 53          Sunday
2010-12-27 53          Monday
2010-12-28 53          Tuesday
2010-12-29 53          Wednesday
2010-12-30 53          Thursday
2010-12-31 53          Friday
2011-01-01 1           Saturday
2011-01-02 2           Sunday
2011-01-03 2           Monday

You are selecting your dates in 7-day increments. The last date that you pulled for 2010 was 2010-12-27, which was accurately being displayed as being in Week 53. But the beginning of the next year occurs within this week on the Saturday, making Saturday Week 1 of 2011, with the following day starting Week 2.

Since you are not selecting a new date until Monday, 2011-01-03, it will effectively skip the dates in the first week of 2011, and begin with Week 2.

Answer 3

Use iso_week in DATEPART:

ISO 8601 includes the ISO week-date system, a numbering system for weeks. Each week is associated with the year in which Thursday occurs. For example, week 1 of 2004 (2004W01) ran from Monday 29 December 2003 to Sunday, 4 January 2004. The highest week number in a year might be 52 or 53. This style of numbering is typically used in European countries/regions, but rare elsewhere.

WITH mycte AS
(
 SELECT DATEADD(ww, DATEDIFF(ww,0,CAST('2010-12-01' AS DATETIME)), 0) DateValue
 UNION ALL
 SELECT  DateValue + 7
 FROM    mycte   
 WHERE   DateValue + 7 < '2016-12-31'
)  
SELECT DATEPART(iso_week, DateValue) as week, DATEPART(year, DateValue) as year,
       DateValue
FROM    mycte
OPTION (MAXRECURSION 0);

LiveDemo

You can also consider changing recursive CTE with tally table.