WEEKOFYEAR

Extract the ISO 8601 week number (1 through 53) from a date.

Category: datetimeReturns: INTEGERDialect: Standard

Syntax

WEEKOFYEAR(date)

Description

## Overview Extracts the ISO 8601 week number from a DATE, returned as an INTEGER between 1 and 53. ISO weeks start on Monday, and the first ISO week of a calendar year is the one that contains the first Thursday of January (equivalently, the week containing January 4). Because ISO weeks do not align with calendar-year boundaries, dates in early January can belong to week 52 or 53 of the previous ISO year, and dates in late December can belong to week 1 of the next ISO year. Always pair WEEKOFYEAR with EXTRACT(ISOYEAR FROM ...) to uniquely identify a week across year boundaries. ## Behavior - Returns an INTEGER in the range 1 through 53. - Returns NULL if the argument is NULL. - Accepts DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE inputs. - Weeks start on Monday. - Week 1 is the week containing the first Thursday of the Gregorian calendar year. - Most ISO years have 52 weeks; some have 53. ## Timezone handling - TIMESTAMP WITH TIME ZONE values are converted to the session zone before the week is taken. ## Compatibility - Follows ISO 8601 week-date semantics (week starts Monday, week 1 contains the first Thursday). Widely adopted in analytical SQL engines.

Parameters

Name	Type	Description
`date`		Specifies the date or timestamp from which to extract the ISO week number.

Examples

-- Mid-March returns week 11
SELECT WEEKOFYEAR(DATE '2025-03-15') AS iso_week;

-- Early January can belong to week 1 of the current year or week 52/53 of the previous year
SELECT WEEKOFYEAR(DATE '2025-01-01') AS iso_week_jan1;

-- Late December can belong to week 1 of the next year
SELECT WEEKOFYEAR(DATE '2025-12-29') AS iso_week_dec29;

-- Orders per ISO week
SELECT WEEKOFYEAR(order_date) AS iso_week, COUNT(*) AS orders
FROM commerce.sales.orders
GROUP BY iso_week
ORDER BY iso_week;

-- Combine with ISO year to build a unique week key
SELECT EXTRACT(ISOYEAR FROM order_date) AS iso_year,
       WEEKOFYEAR(order_date) AS iso_week,
       COUNT(*) AS orders
FROM commerce.sales.orders
GROUP BY iso_year, iso_week
ORDER BY iso_year, iso_week;

Pitfalls

WEEKOFYEAR follows ISO 8601 (Monday as first day of week). Applications that expect Sunday-start weeks will be off by one day and may see different week numbers at the year boundary.
ISO week number alone does not uniquely identify a week. Week 53 of 2020 and week 53 of 2015 are distinct weeks; always pair with EXTRACT(ISOYEAR FROM ...).
Early January dates (for example January 1, 2023) can belong to the previous ISO year. Using YEAR(date) + WEEKOFYEAR(date) as a composite key will misgroup these boundary rows; use ISOYEAR instead.
WEEKOFYEAR ranges up to 53 in rare years. Downstream code that assumes a max of 52 weeks can overflow fixed-size arrays or fail validation.