Get the Week Start Date and Week End Date from Week Number

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 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)

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 iterate week number, start/end date of weeks using moment

<div class="snippet" data-lang="js" data-hide="false" data-console="true" data-babel="false"><div class="snippet-code">

function getISOWeeksInMonth(month, year) {
    let weekStart = new Date(year, month - 1, 1);

    weekStart.setDate(weekStart.getDate() - (weekStart.getDay() || 7) + 1);

    let weekEnd = new Date(weekStart);
    weekEnd.setDate(weekEnd.getDate() + 6);

    let weeks = [];

    do {
        let weekNum = moment(weekStart, "YYYY-MM-DD").week()

        weeks.push({
            weekNum : weekNum,
            start: new Date(weekStart),
            end: new Date(weekEnd)
        });

        weekStart.setDate(weekStart.getDate() + 7);
        weekEnd.setDate(weekEnd.getDate() + 7);
    } while (weekStart.getMonth() < month && (weekStart.getMonth() || (month < 12) ));

    return weeks;
}

let _ = moment.months()
_.forEach(function (month_name) {
    var month_number = moment().month(month_name).format("MM");

    getISOWeeksInMonth(month_number, 2022).forEach(week => console.log(
       'Week : ' + week.weekNum +
       '\nStart: ' + week.start.toDateString() + 
       '\nEnd  : ' + week.end.toDateString())
    );
})

<script src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.29.2/moment.min.js"></script>

Getting start date of week from week number

IIUC, you can use to_datetime:

lst = [25,26,27]
year = 2021

out = pd.to_datetime(pd.Series(lst).astype(str)+str(year)+'Mon', format='%W%Y%a')

output:

0   2021-06-21
1   2021-06-28
2   2021-07-05
dtype: datetime64[ns]

intermediate:

pd.Series(lst).astype(str)+str(year)+'Mon'

0    252021Mon
1    262021Mon
2    272021Mon
dtype: object

PHP get start and end date of a week by weeknumber

Many years ago, I found this function:

function getStartAndEndDate($week, $year) {
  $dto = new DateTime();
  $dto->setISODate($year, $week);
  $ret['week_start'] = $dto->format('Y-m-d');
  $dto->modify('+6 days');
  $ret['week_end'] = $dto->format('Y-m-d');
  return $ret;
}

$week_array = getStartAndEndDate(52,2013);
print_r($week_array);

Generate start date, end date and week numbers belong to given start and end date

These formulas will work in Row 3:

A3  =IF(ISNUMBER($A$1),YEAR(A1),"")
B3  =IF(ISNUMBER($A$1),WEEKNUM($A$1,2),"")
C3  =IF(ISNUMBER($A$1),$A$1-WEEKDAY($A$1,2)+1,"")
D3  =IF(ISNUMBER($A$1),$A$1+6-WEEKDAY($A$1,2)+1,"")

Starting in Row 4, enter these formulas, then use the auto-fill cursor (select cells A4:D4, then click and hold on the lower-right corner icon of the selection box) to copy-fill as many rows down as you need.

A4  =IF(ISNUMBER($D3),IF(($D3+1)<$B$1,YEAR($D3),""),"")
B4  =IF(ISNUMBER($D3),IF(($D3+1)<$B$1,WEEKNUM($D3,2),""),"")
C4  =IF(ISNUMBER($D3), IF( ($D3+1)<$B$1,  $D3+1,""),"")
D4  =IF(ISNUMBER($C4),$C4+6,"")

By the way, you mentioned in your post that you wanted Monday as the start of the week. So I used the value 2 in both WEEKNUM and WEEKDAY. Use a different value for other days if needed.

---

Related Topics