How to get the week number, start date and end date of Compelete year in SQL Server?

Question

How to get the complete week number, startdate and enddate of complete year.

Note

Week starts from Sunday to Saturday
at the same time week no 1 (ex: 01-01-2020 to 04-01-2020) and last week should be (ex:27-12-2020 to 31-12-2020)

Expecting result

WeekNo  WeekStartDate   WeekEndDate
===================================
   1    2019-01-01      2019-01-05
   2    2019-01-06      2019-01-12
   3    2019-01-13      2019-01-19
   4    2019-01-20      2019-01-26
   5    2019-01-27      2019-02-02
   6    2019-02-03      2019-02-09
   7    2019-02-10      2019-02-16
   8    2019-02-17      2019-02-23
   9    2019-02-24      2019-03-02
   ...
   ...upto end of the year

Actually I tried this one also rextester

What would you consider the final week of this year to be (commencing on 29/12/2019 in you Sunday->Saturday week)? Can your year have 53 weeks? — Martin, Nov 07 '19 at 10:36
@Martin yes you are correct. It can be 53 weeks(ex: week 53= from '29/12/2019' to '31/12/2019') — Liam neesan, Nov 07 '19 at 10:41

Martin · Accepted Answer · 2019-11-07T11:08:54.820

If you are interested in 2019 only, then the following code will produce exactly what you are looking for:

DECLARE @weekNum INT = 1;

WITH Weeks AS (
    SELECT @weekNum AS WeekNo
    UNION ALL
    SELECT WeekNo + 1 FROM Weeks WHERE WeekNo + 1 <= 53
)
SELECT  WeekNo,
        CASE
          WHEN WeekStartDate < '2019-01-01' THEN CONVERT(DATE, '2019-01-01')
          ELSE CONVERT(DATE, WeekStartDate)
        END AS WeekStartDate,
        CASE
          WHEN WeekEndDate > '2019-12-31' THEN CONVERT(DATE, '2019-12-31')
          ELSE CONVERT(DATE, WeekEndDate)
        END AS WeekEndDate
  FROM  (
        SELECT  WeekNo,
                DATEADD(WEEK, WeekNo - 1, '2018-12-30') AS WeekStartDate,
                DATEADD(WEEK, WeekNo - 1, '2019-01-05') AS WeekEndDate
          FROM  Weeks
        ) a

OUTPUT:

WeekNo  WeekStartDate   WeekEndDate
1       2019-01-01      2019-01-05
2       2019-01-06      2019-01-12
3       2019-01-13      2019-01-19
4       2019-01-20      2019-01-26
5       2019-01-27      2019-02-02
6       2019-02-03      2019-02-09
7       2019-02-10      2019-02-16
8       2019-02-17      2019-02-23
...
51      2019-12-15      2019-12-21
52      2019-12-22      2019-12-28
53      2019-12-29      2019-12-31

Edit following OP comment about variable start and end dates

Following OP's comment about varying start and end dates, I've revisited the code and made it such that is can work between any two dates:

DECLARE @startDate DATE = CONVERT(DATE, '2019-01-01');
DECLARE @endDate DATE = CONVERT(DATE, '2019-12-31');
DECLARE @weekNum INT = 1;

WITH Weeks AS (
    SELECT @weekNum AS WeekNo
    UNION ALL
    SELECT WeekNo + 1 FROM Weeks WHERE WeekNo + 1 <= DATEDIFF(WEEK, @StartDate, @EndDate) + 1
)
SELECT  WeekNo,
        CASE
          WHEN WeekStartDate < @startDate THEN @startDate
          ELSE CONVERT(DATE, WeekStartDate)
        END AS WeekStartDate,
        CASE
          WHEN WeekEndDate > @endDate THEN @endDate
          ELSE CONVERT(DATE, WeekEndDate)
        END AS WeekEndDate
  FROM  (
        SELECT  WeekNo,
                DATEADD(WEEK, WeekNo - 1, OffsetStartDate) AS WeekStartDate,
                DATEADD(WEEK, WeekNo - 1, OffsetEndDate) AS WeekEndDate
          FROM  Weeks
            INNER JOIN  (
                        SELECT  CASE
                                    WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN @startDate
                                    ELSE DATEADD(DAY, 1 - DATEPART(WEEKDAY, @startDate), @startDate)
                                END AS OffsetStartDate,
                                CASE
                                    WHEN DATEPART(WEEKDAY, @startDate) = 1 THEN DATEADD(DAY, 6, @startDate)
                                    ELSE DATEADD(DAY, 7 - DATEPART(WEEKDAY, @startDate), @startDate)
                                END AS OffsetEndDate
                        ) a ON 1 = 1
        ) a

Simply modify @startDate and @endDate to reflect the desired start and end dates. The format of the string is YYYY-MM-DD.

This will output a variable number of weeks between the two dates, starting and ending on the specified date (creating partial weeks as needed). Hopefully, as per the requirement.

you almost correct. But the user parameter is one StartDate and EndDate, So it can be StartDate = 01-01-2017 and EndDate = 31-12-2023 like this — Liam neesan, Nov 07 '19 at 10:50
@Liamneesan Ah I misunderstood the requirement that the start and end dates could be both variable and in different years. I'll take another look — Martin, Nov 07 '19 at 10:51
@Liamneesan I've updated the answer with a more comprehensive method that works on any date range. — Martin, Nov 07 '19 at 11:09

score 0 · Answer 2 · answered Nov 07 '19 at 11:01

Slightly update on above answer of Martin. You can pass @startDate and @endDate based on your preference.

DECLARE @startDate DATETIME = '2019-01-01'
DECLARE @endDate DATETIME = '2021-01-01'
DECLARE @totalWeeks BIGINT= NULL

SELECT @totalWeeks =datediff(ww,@startdate,@enddate)

DECLARE @weekNum INT = 1;
WITH Weeks AS (
    SELECT @weekNum AS WeekNo
    UNION ALL
    SELECT WeekNo + 1 FROM Weeks WHERE WeekNo + 1 <= @totalWeeks
)

SELECT  WeekNo,
        CASE
          WHEN WeekStartDate < @startDate THEN CONVERT(DATE, '2019-01-01')
          ELSE CONVERT(DATE, WeekStartDate)
        END AS WeekStartDate,
        CASE
          WHEN WeekEndDate > @endDate THEN CONVERT(DATE, '2019-12-31')
          ELSE CONVERT(DATE, WeekEndDate)
        END AS WeekEndDate
  FROM  (
        SELECT  WeekNo,
                DATEADD(WEEK, WeekNo - 1, @startDate) AS WeekStartDate,
                DATEADD(WEEK, WeekNo - 1, @endDate) AS WeekEndDate
          FROM  Weeks
        ) a
    OPTION (MAXRECURSION 1000);

How to get the week number, start date and end date of Compelete year in SQL Server?

2 Answers2