MaxCompute:WEEKOFYEAR

Example 1: Determine the week number for a date at the beginning or end of a year.

If you do not specify the date_part parameter for a date at the beginning or end of a year, the week is assigned to the year that contains most its days.

-- The return value is 52.
SELECT WEEKOFYEAR(TO_DATE('20241229', 'YYYYMMDD'));

-- The week of 2025-12-29 starts on 2025-12-29 (Monday) and ends on 2026-01-04. Most days (four) are in 2026. Therefore, this week is considered the first week of 2026.
-- The return value is 1.
SELECT WEEKOFYEAR(TO_DATE('20251229', 'YYYYMMDD'));

-- 2026-01-01 is in the new year, and most days of its week are in 2026. Therefore, this week is considered the first week of 2026.
-- The return value is 1.
SELECT WEEKOFYEAR(TO_DATE('20260101', 'YYYYMMDD'));

Example 2: Use different rules to specify the first day of a week.

Use the date_part parameter to specify the first day of a week. This affects the week number.

-- The DATE data type is not supported in MaxCompute 1.0. To pass parameters of the DATE type, you must enable MaxCompute 2.0 data types.
SET odps.sql.type.system.odps2=true;
-- By default, a week starts on Monday, and the first week must contain at least four days of the year.
-- The return value is 14.
SELECT WEEKOFYEAR(DATE '2025-03-31');

-- Explicitly set the week to start on Monday.
-- The return value is 13.
SELECT WEEKOFYEAR(DATE '2025-03-31','week(monday)');

-- Set the week to start on Sunday.
-- The return value is 13.
SELECT WEEKOFYEAR(DATETIME '2025-03-31 10:32:00','week(sunday)');

-- Set the week to start on Friday.
-- The return value is 13.
SELECT WEEKOFYEAR(TIMESTAMP_NTZ '2025-03-31 10:32:00.123','week(friday)');

Example 3: Calculate the week number based on the ISO standard.

Use isoweek to follow the ISO 8601 standard. In this standard, a week starts on Monday, and the first week must contain at least four days of the year.

-- The DATE data type is not supported in MaxCompute 1.0. To pass parameters of the DATE type, you must enable MaxCompute 2.0 data types.
SET odps.sql.type.system.odps2=true;
-- According to the ISO week standard, 2025-01-06 is in the second week.
-- The return value is 2.
SELECT WEEKOFYEAR(DATE '2025-01-06','isoweek');

Example 4: Use various date and time data types for the input.

-- The input is of the DATETIME type.
-- The return value is 1.
SELECT WEEKOFYEAR(DATETIME '2025-01-05 10:32:00');

-- The return value is 0.
SELECT WEEKOFYEAR(DATETIME '2025-01-05 10:32:00','week(monday)');

-- The input is a string in a standard date format.
-- The return value is 27.
SELECT WEEKOFYEAR('2025-07-01 12:30:15');

-- The TIMESTAMP data type is not supported in MaxCompute 1.0. To pass parameters of the TIMESTAMP type, you must enable MaxCompute 2.0 data types.
SET odps.sql.type.system.odps2=true;
-- The input is of the TIMESTAMP type.
-- The return value is 30.
SELECT WEEKOFYEAR(TIMESTAMP '2025-07-21 00:30:15.123');

Example 5: Handle abnormal and boundary inputs.

-- The string format does not match yyyy-mm-dd hh:mi:ss and cannot be parsed.
-- The return value is NULL.
SELECT WEEKOFYEAR('20141231');

-- The return value is NULL.
SELECT WEEKOFYEAR(NULL);