SQLTeam.com | Weblogs | Forums

Date from Week Number and Day

Is there a way I can get the date a certain day of the week falls on?

To explain better. The information I have Monday of Week 38 2020. How can I get it to return that the date for that day is 14/09/2020?

Further to this
=MAX(DATE(2020,1,1),DATE(2020,1,1)-WEEKDAY(DATE(2020,1,1),1)+(C1-1)*7+1)

Is what I use in Excel. What is the SQL version?

hi

hope this helps . please see

DECLARE @WeekNum INT
      , @YearNum char(4);

SELECT @WeekNum = 38
     , @YearNum = '2020'

SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 7) AS MondayOfWeek;

image

DECLARE @week_number int
SET @week_number = 38

SELECT DATEADD(DAY, (@week_number - 1) * 7, first_mon_of_year)
FROM (
    SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS jan_01
) AS calc1
CROSS APPLY (
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 0, jan_01) % 7, jan_01) AS first_mon_of_year
) AS calc2