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 returns the month in which this calendar week starts (i.e. if the Monday is the 31th of July, it should return "7"). I haven't found a built-in function to achieve this task and I have no idea to implement this easily. So I hope for your help and ideas!
Thanks in advance, Melvin
Query
DECLARE @WEEK INT;
DECLARE @YEAR INT;
SET @week = 3
SET @year = 2014
SELECT 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 ║
╚═══════╝
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 @Week int, @Year int, @Date datetime;
SET @Week = 1
SET @Year = 2014
SET @Date = CAST(@Year as varchar(4)) + '-01-01'
SELECT @Date = DATEADD(ww, @week-1, @Date)
SELECT MONTH(DATEADD(d, (DATEPART(dw,@date)-2)*-1, @date)),
CASE WHEN @Week = 1 AND MONTH(DATEADD(d, (DATEPART(dw,@date)-2)*-1, @date)) = 12
THEN @YEAR - 1 ELSE @YEAR END as CorrectYear
