Extract the day-of-month (1 through 31) from a date.
DAYOFMONTH(date)## Overview Extracts the day-of-month component from a DATE or TIMESTAMP, returned as an INTEGER between 1 and 31. Equivalent to EXTRACT(DAY FROM input). Use DAYOFMONTH for day-of-month distribution analysis, filtering month-end/month-start activity, or extracting the day portion for format strings and display logic. ## Behavior - Returns an INTEGER in the range 1 through 31 (value depends on the actual month length). - Returns NULL if the argument is NULL. - Accepts DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE inputs. - Day numbering is 1-based. ## Timezone handling - TIMESTAMP WITH TIME ZONE values are converted to the session zone before the day is taken. - Near-midnight timestamps can resolve to different days in different session zones. ## Compatibility - Equivalent to EXTRACT(DAY FROM source). DAYOFMONTH is a widely supported convenience function.
| Name | Type | Description |
|---|---|---|
date | Specifies the date or timestamp from which to extract the day-of-month component. |
-- Mid-month returns 15
SELECT DAYOFMONTH(DATE '2025-03-15') AS dom;-- Last day of February in a non-leap year
SELECT DAYOFMONTH(DATE '2025-02-28') AS dom;-- Today's day-of-month
SELECT DAYOFMONTH(CURRENT_DATE()) AS today_dom;-- Filter invoices billed on the first of the month
SELECT invoice_id, invoice_date
FROM billing.accounts.invoices
WHERE DAYOFMONTH(invoice_date) = 1;-- Distribution of customer activity across days of the month
SELECT DAYOFMONTH(event_ts) AS dom, COUNT(*) AS events
FROM analytics.events.user_activity
GROUP BY dom
ORDER BY dom;