MaxCompute:WEEKOFYEAR

Example 1: Year-boundary week assignment

When date_part is omitted, a week at the year boundary belongs to the year that contains the majority of its days.

-- 2024-12-29 is in a week whose majority falls in 2024.
-- Returns 52.
SELECT WEEKOFYEAR(TO_DATE('20241229', 'YYYYMMDD'));

-- The week of 2025-12-29 runs from Monday 2025-12-29 to Sunday 2026-01-04.
-- Four of the seven days fall in 2026, so this week is week 1 of 2026.
-- Returns 1.
SELECT WEEKOFYEAR(TO_DATE('20251229', 'YYYYMMDD'));

-- 2026-01-01 falls in the same week as above — week 1 of 2026.
-- Returns 1.
SELECT WEEKOFYEAR(TO_DATE('20260101', 'YYYYMMDD'));

Example 2: Controlling the first day of the week

The date_part parameter changes which week a date falls in. Note that passing week(monday) explicitly produces different results from the default (no date_part), even though both use Monday as the week start.

-- Enable MaxCompute 2.0 data types to use the DATE type.
SET odps.sql.type.system.odps2=true;

-- Default (no date_part): Monday start with the four-day threshold.
-- Returns 14.
SELECT WEEKOFYEAR(DATE '2025-03-31');

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

-- Week starts on Sunday.
-- Returns 13.
SELECT WEEKOFYEAR(DATETIME '2025-03-31 10:32:00', 'week(sunday)');

-- Week starts on Friday.
-- Returns 13.
SELECT WEEKOFYEAR(TIMESTAMP_NTZ '2025-03-31 10:32:00.123', 'week(friday)');

Example 3: ISO week numbering

Use isoweek for ISO 8601 week numbering. Weeks start on Monday and the first week of the year must contain at least four days.

SET odps.sql.type.system.odps2=true;

-- Per ISO 8601, 2025-01-06 falls in week 2.
-- Returns 2.
SELECT WEEKOFYEAR(DATE '2025-01-06', 'isoweek');

Example 4: Input type variations

-- DATETIME input returns BIGINT.
-- Returns 1.
SELECT WEEKOFYEAR(DATETIME '2025-01-05 10:32:00');

-- DATETIME with explicit week(monday).
-- Returns 0.
SELECT WEEKOFYEAR(DATETIME '2025-01-05 10:32:00', 'week(monday)');

-- STRING input in yyyy-mm-dd hh:mi:ss format.
-- Returns 27.
SELECT WEEKOFYEAR('2025-07-01 12:30:15');

-- TIMESTAMP input (requires MaxCompute 2.0 data types).
SET odps.sql.type.system.odps2=true;
-- Returns 30.
SELECT WEEKOFYEAR(TIMESTAMP '2025-07-21 00:30:15.123');

Example 5: NULL and invalid inputs

-- String does not match yyyy-mm-dd hh:mi:ss format.
-- Returns NULL.
SELECT WEEKOFYEAR('20141231');

-- NULL input returns NULL.
SELECT WEEKOFYEAR(NULL);