Get Start and End Date from Week Number SQL Server

Get dates from a week number in T-SQL

Quassnoi's answer works, but kind of leaves you on the hook for cleaning up the dates if they are dates in the middle of the day (his start of week leaves you one day earlier than you need to be if you use a time in the middle of the day -- you can test using GETDATE()).

I've used something like this in the past:

SELECT 
   CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, DATECOL), DATECOL)), 101),
   CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, DATECOL) - 6, DATECOL)), 101)

A side benefit of this is that by using @@DATEFIRST you can handle nonstandard week starting days (the default is Sunday, but with SET @@DATEFIRST you can change this).

It seems crazy that simple date manipulation in SQL Server has to be this arcane, but there you go...

How to get start date and end date by selecting week number in SQL Server

In addition to a week number, you will need to include a year number:

declare @wk int  set @wk = 40
declare @yr int  set @yr = 2020

select dateadd (week, @wk-1, dateadd (year, @yr-1900, 0)) - 4 -
       datepart(dw, dateadd (week, @wk-1, dateadd (year, @yr-1900, 0)) - 4) + 1 StartDate,
    dateadd (week, @wk-1, dateadd (year, @yr-1900, 0)) - 4 -
       datepart(dw, dateadd (week, @wk-1, dateadd (year, @yr-1900, 0)) - 4) + 6 EndDate

StartDate               EndDate
2020-09-26 00:00:00.000 2020-10-01 00:00:00.000

Slightly modified from this great answer: SQL Convert Week Number to Date (dd/MM)

Get the week start date and week end date from week number

You can find the day of week and do a date add on days to get the start and end dates..

DATEADD(dd, -(DATEPART(dw, WeddingDate)-1), WeddingDate) [WeekStart]

DATEADD(dd, 7-(DATEPART(dw, WeddingDate)), WeddingDate) [WeekEnd]

You probably also want to look at stripping off the time from the date as well though.

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

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.

How to get the week Starting and Ending date using Week number in SQL Server?

I tried like this ....

 DECLARE @WK INT  SET @WK = 32
    DECLARE @YR INT  SET @YR = 2017

    SELECT DATEADD (WEEK, @WK, DATEADD (YEAR, @YR-1900, 0)) - 4 -
             DATEPART(DW, DATEADD (WEEK, @WK, DATEADD (YEAR, @YR-1900, 0)) - 4) + 1 AS [WEEK_STARTDATE],

     DATEADD (WEEK, @WK, DATEADD (YEAR, @YR-1900, 0)) - 4 -
             DATEPART(DW, DATEADD (WEEK, @WK, DATEADD (YEAR, @YR-1900, 0)) - 4) + 7 AS [WEEK_ENDDATEDATE]

output

WEEK_STARTDATE            WEEK_ENDDATEDATE
2017-08-06 00:00:00.000   2017-08-12 00:00:00.000

how to get last 6 week start date and end date in MSSQL

Try storing the current week start / end in variables and then use DATEADD to calculate the previous weeks:

declare @weekStart date = cast(DATEADD(dd, -(DATEPART(dw, getdate())-1), getdate()) as date)
declare @weekEnd date = cast(DATEADD(dd, 7-(DATEPART(dw, getdate())), getdate()) as date)

select @weekStart, @weekEnd
union all
select DATEADD(dd, -7, @weekStart), DATEADD(dd, -7, @weekEnd)
union all
select DATEADD(dd, -7 * 2, @weekStart), DATEADD(dd, -7  * 2, @weekEnd)

Demo here

---

Related Topics