Extract the ordinal day of the year (1 through 366) from a date.
DAYOFYEAR(date)## Overview Extracts the ordinal day-of-year from a DATE or TIMESTAMP, returned as an INTEGER between 1 and 366. January 1 is day 1, February 1 is day 32, and in a leap year December 31 is day 366. Use DAYOFYEAR for year-to-date calculations, cross-year seasonality comparisons (same ordinal day across multiple years), and pacing or progress metrics. ## Behavior - Returns an INTEGER in the range 1 through 366. - Returns NULL if the argument is NULL. - Accepts DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE inputs. - Leap years contain 366 days; non-leap years contain 365. December 31 is day 365 in non-leap years, day 366 in leap years. - Numbering is 1-based (not 0-based). ## Timezone handling - TIMESTAMP WITH TIME ZONE values are converted to the session zone before the day-of-year is taken. ## Compatibility - Equivalent to EXTRACT(DOY FROM source) in the SQL standard.
| Name | Type | Description |
|---|---|---|
date | Specifies the date or timestamp from which to extract the day-of-year. |
-- Day 74 of 2025
SELECT DAYOFYEAR(DATE '2025-03-15') AS doy;-- First day of year
SELECT DAYOFYEAR(DATE '2025-01-01') AS doy;-- Last day of a leap year (day 366)
SELECT DAYOFYEAR(DATE '2024-12-31') AS doy;-- Percentage of the year elapsed as of today
SELECT DAYOFYEAR(CURRENT_DATE()) / 365.0 * 100 AS pct_elapsed;-- Compare same ordinal day across years
SELECT YEAR(order_date) AS yr, COUNT(*) AS orders
FROM commerce.sales.orders
WHERE DAYOFYEAR(order_date) = DAYOFYEAR(CURRENT_DATE())
GROUP BY yr
ORDER BY yr;