SQL Server: how to convert SAP-style CALWEEK to CALMONTH?

Question

The year 2015 has 53 weeks while the year 2016 has 52 weeks. Hence, the CALMONTH cannot be calculated by the week number divided by four and then ceiling it. CALWEEK is reported such as 201652, 201501 and 201104. CALMONTH is a number between a range %YEAR%MONTHsuch as 201501 and 201512 for the year 2015.

How can I convert the SAP-style CALWEEK to CALMONTH in SQL Server 2014?

EDIT (from OP comments):

"I have data with SAP fields to be edited in SQL Server. I need to convert CALWEEK to CALMONTH. The beginning data comes with CALMONTH and CALWEEK fields so I could try to do some JOIN to get back from WEEKs to MONTHs. I always do casting because every field is in VARCHAR even though CALMONTHs/CALWEEKs"

Answer 1

Maybe I'm crazy, but I thought this was kind of fun to figure out. I had to tear the string apart and get the date of the first day of the week. Then figured out the month and pieced it back to a string

CREATE TABLE #f(
  CALWEEK VARCHAR(10));

INSERT #f (CALWEEK)
VALUES ('201602'),('201606'),('201612'),('201624'),('201630'),
   ('201632'),('201650'),('201652'),('201701'),('201706'),('201715');


SELECT CALWEEK,
    --GETTING THE YEAR
    CAST(DATEPART(yyyy,DATEADD(wk,DATEDIFF(wk,6,'1/1/' + 
    SUBSTRING(CALWEEK,1,4)) +
    (SUBSTRING(CALWEEK,5,6)-1), 6))AS VARCHAR(4))+
    --GETTING THE MONTH
    CASE WHEN LEN(
    CAST(DATEPART(mm,DATEADD(wk,DATEDIFF(wk,6,'1/1/' + 
    SUBSTRING(CALWEEK,1,4)) +
    (SUBSTRING(CALWEEK,5,6)-1), 6)) AS VARCHAR(2))) = 1 
    THEN '0' + CAST(DATEPART(mm,DATEADD(wk,DATEDIFF(wk,6,'1/1/' + 
    SUBSTRING(CALWEEK,1,4)) +
    (SUBSTRING(CALWEEK,5,6)-1), 6)) AS VARCHAR(2))
    ELSE CAST(DATEPART(mm,DATEADD(wk,DATEDIFF(wk,6,'1/1/' + 
    SUBSTRING(CALWEEK,1,4)) +
    (SUBSTRING(CALWEEK,5,6)-1), 6)) AS VARCHAR(2)) END AS 'CALMONTH'
FROM #f

DROP TABLE #f