Does anyone know how to calculate the week of the month? I can get the week of the year but need to know the week of each month. The values would be between 1-5.

Thanks

This message has been edited. Last edited by: Trudy,

If your week in month number always starts on the first of the month, then -

month_wk/I2 = INT(day_of_month / 7) + 1;

T

Hi Trudy, hope you been well.

I know in T-SQL world this works:

declare @date_given datetime = '2020-04-17'
select (DATEPART(wk,@date_given)-DATEPART(wk,dateadd(d,1-day(@date_given),@date_given)))+1

http://sqlfiddle.com/#!18/a7540/27697

For FOCUS, I think it would be in the neighborhood of

  DEFINE FILE CAR
MONTH_DATE/I2 = (DPART(&YYMD, 'WEEK', 'I11')-DPART(DATEADD(&YYMD, 'D', +1), 'WEEK', 'I11'))+1;

Sorry haven't had enough caffine to finish that one out, but maybe it points you in the right direciton.

Tony,

Not sure I understand how your post works. If the 1st day of the month falls on a Friday then the 4th would be on the Monday of the following week so the 1st would be week 1 and the 4th should be week 2 but with your calculation wouldn't they both still be week 1? Maybe I am not understanding what day_of_month is in your example.

Precisely why I stated "If your week in month number always starts on the first of the month".

From your reply, it is now clear that you want your weeks to run from Monday to Sunday - you didn't state that in your question, so I had to guess!

Perhaps obtain the week number within the year from the 1st of the month and take this value away from the week number of the other days within the month?

Use DATEMOV(date in YYMD, 'BOM') to get the begging of the month for the date in your data.
Use DTPART(date in YYMD, WEEK) to get a 2 digit week number for both the beginning of the month and the date in your data.
Finally work out which week number a date falls within (second date minus week of first of month).

Remember that dates may belong to a week from the previous year.

Check out using ISO dates depending upon your requirement also the setting of WEEKFIRST.

Good luck

T

This message has been edited. Last edited by: Tony A,

As an example and tester, use the following code to create a temporary table for every date in the current year -

FILEDEF DATEMAS DISK DATERNG.MAS
-RUN
-WRITE DATEMAS FILE=DATERNG,SUFFIX=XFOC
-WRITE DATEMAS SEGNAME=SEG1
-WRITE DATEMAS FIELD=DATE_KEY, ,DMYY ,DMYY , $
-RUN
-* Now create it
CREATE FILE DATERNG
-* and add all possible dates within a month
MODIFY FILE DATERNG
FREEFORM DATE_KEY.A8.
LOG FORMAT MSG OFF
LOG TRANS MSG OFF
LOG INVALID MSG OFF
-* Any invalid dates will be rejected.
-DEFAULT &Year  = 2020
-SET &Year  = EDIT(&YYMD,'9999$');
DATA
-* This repeat loop will ensure at least a whole year is input,
-SET &Month = 1;
-SET &Day   = 1;
-REPEAT :Loop0 12 TIMES;
-SET &Month = FPRINT(&Month, 'I2L', 'A2');
-REPEAT :Loop1 31 TIMES;
-SET &Date = FPRINT(&Day, 'I2L', 'A2');
&Date&Month&Year
-SET &Day = &Day + 1;
-:Loop1
-SET &Day = 1;
-SET &Month = &Month + 1;
-:Loop0
END
-RUN

Then use the following code to test the week in month calculation -

-* Set the first day of week to Mondays
SET WEEKFIRST = 2
TABLE FILE DATERNG
SUM COMPUTE FSTOFMNTH/YYMD = DATEMOV(DATE_KEY, 'BOM'); NOPRINT
    COMPUTE FSTWEEK/I2     = DTPART(FSTOFMNTH, WEEK); NOPRINT
    COMPUTE CURWEEK/I2     = DTPART(DATE_KEY, WEEK); NOPRINT
    COMPUTE DAYOFWK/A3     = DOWK(DATECVT(DATE_KEY, 'YYMD', 'I8YYMD'), 'A3');
    COMPUTE WEEKINMNTH/I2  = IF FSTWEEK GE 52 THEN CURWEEK - ((CURWEEK GE 52) * CURWEEK) + 1 ELSE CURWEEK - FSTWEEK + 1;
 BY DATE
END
-RUN

T

Here's some code that I created and used for my Financial Calendar back in 2016... It's just the basis and should still work today.

SET WEEKFIRST = 1 
SET WEEKFIRST = ISO2
...
DEFINE FILE InputFile
RPTDATE/A8MDYY = TRANSDATE ;
...
-* Define MONTH...
-* The day of week (DOW) gives us the column number but this is Sunday thru Saturday
-* and we require Monday thru Sunday
DOWX/W     = TRANSDATE;
DOWNMBR/W      = IF DOWX = 7 THEN 1 ELSE DOWX + 1 ;
DAYNAME/A9 = DECODE DOW (1 Sunday 2 Monday 3 Tuesday 4 Wednesday 5 Thursday 6 Friday 7 Saturday) ;
...
END
TABLE FILE InputFile
SUM
FIELD1 FIELD2 ...
BY MONTH
ACROSS DOWNMBR NOPORINT
ACROSS DOWNAME AS 'Whatever'
END
...

Thanks to everyone. I used the following code to get the two weeks like Tony suggested and then subtracted them to get the WEEKOFMONTH. Works beautifully.

     COMPUTE DT2/A20 = FPRINT(SFRSTCR_ADD_DATE, 'HYYMDS', 'A20');
     COMPUTE MONTH_NUM2/A2 = SUBSTR(20, DT2, 6, 7, 2, 'A2');
     COMPUTE MONTH2/A11 = DECODE MONTH_NUM2( '01' 'January' '02' 'Febuary' '03' 'March' '04' 'April' '05' 'May' '06' 'June' '07' 'July' '08' 'August' '09' 'September' '10' 'October' '11' 'November' ELSE 'December' ); NOPRINT
     COMPUTE ISODATE2/A10 = HYYWD(SFRSTCR_ADD_DATE, 'A10');
     COMPUTE WEEK2/I2 = EDIT(SUBSTR(10, ISODATE2, 7, 9, 2, 'A2'));
     COMPUTE monthdate/I11 = DPART(AddDateYYMD, 'WEEK', 'I11');
     COMPUTE BOMDT/HYYMD = DATEMOV(SFRSTCR.SFRSTCR.AddDateYYMD, 'BOM');
	 COMPUTE DT1/A20 = FPRINT(BOMDT, 'HYYMDS', 'A20');
	 COMPUTE MONTH_NUM1/A2 = SUBSTR(20, DT1, 6, 7, 2, 'A2');
	 COMPUTE MONTH1/A11 = DECODE MONTH_NUM1('01' 'January' '02' 'Febuary' '03' 'March' '04' 'April' '05' 'May' '06' 'June' '07' 'July' '08' 'August' '09' 'September' '10' 'October' '11' 'November' ELSE 'December'); NOPRINT
	 COMPUTE ISODATE1/A10 = HYYWD(BOMDT, 'A10');
     COMPUTE WEEK1/I2 = EDIT(SUBSTR(10, ISODATE1, 7, 9, 2, 'A2'));
     COMPUTE WEEKOFMONTH/I1=WEEK2-WEEK1+1;
  

Sorry folks realized I posted extra field calculations that I need for my report that aren't necessary for the week of month calculation so below is the cleaner code.

     COMPUTE DT2/A20 = FPRINT(SFRSTCR_ADD_DATE, 'HYYMDS', 'A20');
     COMPUTE ISODATE2/A10 = HYYWD(SFRSTCR_ADD_DATE, 'A10');
     COMPUTE WEEK2/I2 = EDIT(SUBSTR(10, ISODATE2, 7, 9, 2, 'A2'));
     COMPUTE BOMDT/HYYMD = DATEMOV(SFRSTCR.SFRSTCR.AddDateYYMD, 'BOM');
     COMPUTE DT1/A20 = FPRINT(BOMDT, 'HYYMDS', 'A20');
     COMPUTE ISODATE1/A10 = HYYWD(BOMDT, 'A10');
     COMPUTE WEEK1/I2 = EDIT(SUBSTR(10, ISODATE1, 7, 9, 2, 'A2'));
     COMPUTE WEEKOFMONTH/I1=WEEK2-WEEK1+1;
  

Trudy, I tried your code. It does not seem to work for the month of January. Am I missing something?

quote:

Am I missing something?

DB, No, you're not missing anything. There is no allowance for the first week of the year belonging to the last week of the previous year. I mentioned that this needs to be allowed for in my initial post.

Using the following in Trudy's code will sort that out -

COMPUTE WEEKOFMNTH/I2  = IF WEEK1 GE 52 THEN WEEK2 - ((WEEK2 GE 52) * WEEK2) + 1 ELSE WEEK2 - WEEK1 + 1;

T

Perhaps you want to have the weeks run from Saturday to Friday to avoid having a weekday occur in a week number 6. I think the following code should work. It allows you to specify which day of the week is the start of a week. The last three COMPUTE statements accomplish the week of month calculation. The first sections of the code generate a file with dates for testing.

/* Can set the day that represents the first day of the week. */
/* Week Start Day 1=Mon 2=Tue 3=Wed 4=Thu 5=Fri 6=Sat 7=Sun   */

-SET &Weekstart = 6;
-SET &StartDate = &DATEYY | '0101';
-SET &EndDate   = &DATEYY | '1231';
-SET &Date      = &StartDate;
-SET &i         = 1;

 SQL CREATE TABLE calendar
 ( KEY      INTEGER,
   DATEYYMD DATE    );
 END

 MODIFY FILE calendar
 MATCH KEY
 ON MATCH REJECT
 ON NOMATCH INCLUDE
 DATA
-REPEAT ENDREPEAT WHILE &EndDate GE &Date ;
 &i,'&Date',$
-SET &i = &i + 1;
-SET &Date = AYMD(&Date, 1, 'I8YYMD');
-ENDREPEAT
 END

 DEFINE FILE calendar
  DATEMYY/MYY = DATEYYMD;
 END
 TABLE FILE calendar
 PRINT COMPUTE DATEW/W     = DATEYYMD;
       COMPUTE DATEWtr/Wtr = DATEYYMD;
       COMPUTE DATED/D     = DATEYYMD; NOPRINT
       COMPUTE DATEBOMW/W  = DATEMOV(DATEYYMD, 'BOM'); NOPRINT
       COMPUTE Week/I2     = ( DATED - 1 + MOD(DATEBOMW - &Weekstart + 7, 7) ) / 7 + 1 ;
 BY DATEMYY NOPRINT
 BY DATEYYMD
 ON DATEMYY PAGE-BREAK
 END