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"

What do you actually need? You can't convert weeks to months, because it can be 1 or 2 months. Please add a proper example instead of just referring to SAP functions. — James Z, Apr 30 '17 at 13:55
@JamesZ what? 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. — hhh, Apr 30 '17 at 14:30
@Jason I always do casting because every field is in varcher even though CALMONTHs/CALWEEKs. — hhh, Apr 30 '17 at 14:38
Still no idea about what you're trying to do. Weeks do not match months, so you can't just convert a week into a month without giving more details. Do you use Monday? Wednesday? Sunday? Why don't you give concrete examples about what you're trying to do? — James Z, Apr 30 '17 at 15:57

score 0 · Accepted Answer · answered Apr 30 '17 at 15:42

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

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

1 Answers1