Day-of-week is the classic cross-dialect trap: everyone numbers the week differently.
Day-of-week NUMBER
-- MySQL: DAYOFWEEK = 1(Sun)..7(Sat) | WEEKDAY = 0(Mon)..6(Sun)
SELECT DAYOFWEEK(d), WEEKDAY(d);
-- PostgreSQL: DOW = 0(Sun)..6(Sat) | ISODOW = 1(Mon)..7(Sun)
SELECT EXTRACT(DOW FROM d), EXTRACT(ISODOW FROM d);
-- BigQuery: DAYOFWEEK = 1(Sun)..7(Sat)
SELECT EXTRACT(DAYOFWEEK FROM d);
-- Snowflake: DAYOFWEEK = 0(Sun)..6(Sat) by default (DAYOFWEEKISO = 1(Mon)..7(Sun))
SELECT DAYOFWEEK(d), DAYOFWEEKISO(d);
-- SQLite: %w = 0(Sun)..6(Sat)
SELECT CAST(strftime('%w', d) AS INT);
-- DuckDB: dayofweek = 0(Sun)..6(Sat) | isodow = 1(Mon)..7(Sun)
SELECT dayofweek(d), isodow(d);
Weekday NAME
SELECT DAYNAME(d); -- MySQL / Snowflake / DuckDB
SELECT TO_CHAR(d, 'Day'); -- PostgreSQL (padded; use FMDay to trim)
SELECT FORMAT_DATE('%A', d); -- BigQuery
SELECT strftime('%w', d); -- SQLite has no name token (map %w yourself)
ISO week number
SELECT EXTRACT(WEEK FROM d); -- Postgres (ISO), DuckDB
SELECT EXTRACT(ISOWEEK FROM d); -- BigQuery
SELECT WEEKISO(d); -- Snowflake
SELECT WEEK(d, 3); -- MySQL (mode 3 = ISO)
SELECT CAST(strftime('%W', d) AS INT); -- SQLite (approx; not ISO)
The trap: if you compare a day-of-week number across engines, confirm the base — Sunday is 1
in MySQL/BigQuery but 0 in Postgres DOW/SQLite/DuckDB, and the ISO variants start Monday at
1. Prefer the ISODOW/DAYOFWEEKISO (Monday=1) forms when you need a stable convention.