Get Month From Calendar Week (sql Server)

I have the following problem: In Microsoft's SQL Server, I have a table where a year and a calendar week (starting with Monday) is stored. Now, I want to write a function which ret

Solution 1:

Query

DECLARE@WEEKINT;
DECLARE@YEARINT;
SET@week=3SET@year=2014SELECT DATEPART(MM,CAST(CONVERT(CHAR(3),
                 DATEADD(WW,@WEEK-1,
                 CONVERT(datetime,'01/01/'+CONVERT(char(4),@Year)))
                 ,100)+' 1900'AS DATETIME)) AS [MONTH]

Result

╔═══════╗
║ MONTH ║
╠═══════╣
║     1 ║
╚═══════╝

Solution 2:

Edited: I had to reread answer and pull from M.Ali to get the full answer. You also have to know the correct year if the first week starts in the previous year. You may need to do some editing if your first week of the year doesn't necessarily include 1 Jan.

DECLARE@Weekint, @Yearint, @Date datetime;

SET@Week=1SET@Year=2014SET@Date=CAST(@Yearasvarchar(4)) +'-01-01'SELECT@Date= DATEADD(ww, @week-1, @Date)

SELECTMONTH(DATEADD(d, (DATEPART(dw,@date)-2)*-1, @date)), 
  CASEWHEN@Week=1ANDMONTH(DATEADD(d, (DATEPART(dw,@date)-2)*-1, @date)) =12THEN@YEAR-1ELSE@YEARENDas CorrectYear