Extract the day-of-week number from a date, with Sunday as 1 and Saturday as 7.
DAYOFWEEK(date)## Overview Extracts the day-of-week from a DATE or TIMESTAMP, returned as an INTEGER. DeltaForge uses the ODBC convention: Sunday = 1, Monday = 2, ..., Saturday = 7. Use DAYOFWEEK for weekday/weekend categorization, day-of-week cohort analysis, and scheduling logic that depends on the calendar day. ## Behavior - Returns an INTEGER in the range 1 through 7. - Returns NULL if the argument is NULL. - Numbering: Sunday = 1, Monday = 2, Tuesday = 3, Wednesday = 4, Thursday = 5, Friday = 6, Saturday = 7. - Accepts DATE, TIMESTAMP, and TIMESTAMP WITH TIME ZONE inputs. - Stable across locale settings; does not shift based on session locale. ## Timezone handling - TIMESTAMP WITH TIME ZONE values are converted to the session zone before the day-of-week is taken. Near-midnight instants can resolve to different weekdays in different zones. ## Compatibility - Uses ODBC/ANSI numbering (Sunday = 1). For ISO 8601 numbering (Monday = 1, Sunday = 7) use EXTRACT(ISODOW FROM ...) when supported or adjust with a CASE expression.
| Name | Type | Description |
|---|---|---|
date | Specifies the date or timestamp from which to extract the day-of-week. |
-- Numeric day of week (1 = Sunday, 7 = Saturday)
SELECT DAYOFWEEK(DATE '2025-03-15') AS dow;-- Tag weekend vs weekday
SELECT CASE WHEN DAYOFWEEK(DATE '2025-03-15') IN (1, 7) THEN 'weekend' ELSE 'weekday' END AS kind;-- Weekday-only order rows (Mon through Fri = 2..6)
SELECT order_id
FROM commerce.sales.orders
WHERE DAYOFWEEK(order_date) BETWEEN 2 AND 6;-- Orders by day of week
SELECT DAYOFWEEK(order_date) AS dow, COUNT(*) AS orders
FROM commerce.sales.orders
GROUP BY dow
ORDER BY dow;-- Convert to Monday-based numbering (Monday = 1, Sunday = 7)
SELECT CASE WHEN DAYOFWEEK(DATE '2025-03-15') = 1 THEN 7 ELSE DAYOFWEEK(DATE '2025-03-15') - 1 END AS mon_based_dow;