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¶
- Source code
- WEEKSINMONTH scalar function
- MONTH (built-in function)
- WEEK_ISO (built-in function)