WEEKSINMONTH_ISO scalar function

Returns the number of weeks within the month that ADATE exists within, or the number of weeks in AMONTH in AYEAR.

Prototypes

WEEKSINMONTH_ISO(AYEAR INTEGER, AMONTH INTEGER)
WEEKSINMONTH_ISO(ADATE DATE)
WEEKSINMONTH_ISO(ADATE TIMESTAMP)
WEEKSINMONTH_ISO(ADATE VARCHAR(26))

RETURNS SMALLINT

Description

Returns the number of weeks in AMONTH in AYEAR (weeks start on a Monday, and partial weeks are permitted at the start and end of the month), or the number of weeks in the month that ADATE exists within depending on the variant of the function that is called.

Note

As far as I’m aware, ISO8601 doesn’t say anything about weeks within a month, hence why this function differs from WEEKSINYEAR_ISO scalar function which does not permit partial weeks at the start and end of a year. This function simply mirrors the functionality of WEEKSINMONTH scalar function but with a definition of weeks that start on a Monday instead of Sunday.

Parameters

AYEAR
If provided, the year containing AMONTH for which to calculate the number of weeks.
AMONTH
If provided, the month within AYEAR for which to calculate the number of weeks.
ADATE
If provided, the date within the month for which to calculate the number of weeks. Either AYEAR and AMONTH, or ADATE must be provided.

Examples

Calculate the number of weeks in January 2010:

VALUES WEEKSINMONTH_ISO(2010, 1);
1
------
     5

Calculate the number of weeks in the months of 2010:

SELECT MONTH(D) AS MONTH, WEEKSINMONTH_ISO(D) AS WEEKS
FROM TABLE(DATE_RANGE('2010-01-01', '2010-12-01', 100));
MONTH       WEEKS
----------- ------
          1      5
          2      4
          3      5
          4      5
          5      6
          6      5
          7      5
          8      6
          9      5
         10      5
         11      5
         12      5

See Also