Forgly

SQL to get the day of the week

Get the day of the week from a date in SQL — the weekday number or the day name (Monday, Tuesday…) in PostgreSQL, MySQL, SQL Server, and SQLite.

PostgreSQL
SELECT order_date,
       EXTRACT(DOW FROM order_date) AS dow,   -- 0 = Sunday … 6 = Saturday
       TO_CHAR(order_date, 'FMDay')   AS day_name
FROM orders;

EXTRACT(DOW …) returns 0 for Sunday; use EXTRACT(ISODOW …) for 1 = Monday … 7 = Sunday.

MySQL
SELECT order_date,
       DAYOFWEEK(order_date) AS dow,   -- 1 = Sunday … 7 = Saturday
       DAYNAME(order_date)   AS day_name
FROM orders;

DAYOFWEEK() starts at 1 = Sunday; WEEKDAY() returns 0 = Monday … 6 = Sunday if you want a Monday-based number.

SQL Server
SELECT order_date,
       DATEPART(WEEKDAY, order_date) AS dow,
       DATENAME(WEEKDAY, order_date) AS day_name
FROM orders;

DATEPART(WEEKDAY …) depends on SET DATEFIRST / the session language; run SET DATEFIRST 1 to force Monday = 1. DATENAME returns the localized day name.

SQLite
SELECT order_date,
       strftime('%w', order_date) AS dow   -- '0' = Sunday … '6' = Saturday
FROM orders;

SQLite has no day-name function; strftime('%w') gives 0 = Sunday. Map it to a name with a CASE expression.

How to get the day of the week from a date

Getting the day of the week turns a date into either its weekday number or its name (Monday, Tuesday, …). Each database has its own function — and they disagree on which day is 0 or 1 — so the exact expression matters.

How it works

  • Every dialect exposes the weekday as a number — the catch is the numbering: PostgreSQL and SQLite start at 0 = Sunday, MySQL's DAYOFWEEK starts at 1 = Sunday, and SQL Server depends on the DATEFIRST setting.
  • For an ISO week (Monday = 1 … Sunday = 7) use EXTRACT(ISODOW FROM …) in PostgreSQL or WEEKDAY() + 1 in MySQL.
  • For the name (Monday, Tuesday…) use TO_CHAR(…, 'Day') in PostgreSQL, DAYNAME() in MySQL, and DATENAME(WEEKDAY, …) in SQL Server.
  • Group by the weekday expression to answer "which day of the week is busiest?" — convert to one consistent time zone first, or late-night rows can land on the wrong day.

Tip

Don't hard-code weekday numbers across databases — Sunday is 0, 1, or DATEFIRST-dependent depending on the engine. Compare on the day name, or normalize to ISO (Monday = 1) before you compare.

Related SQL queries

Frequently asked questions

How do I get the day of the week from a date in SQL?

Getting the day of the week turns a date into either its weekday number or its name (Monday, Tuesday, …). Each database has its own function — and they disagree on which day is 0 or 1 — so the exact expression matters. Every dialect exposes the weekday as a number — the catch is the numbering: PostgreSQL and SQLite start at 0 = Sunday, MySQL's DAYOFWEEK starts at 1 = Sunday, and SQL Server depends on the DATEFIRST setting. For an ISO week (Monday = 1 … Sunday = 7) use EXTRACT(ISODOW FROM …) in PostgreSQL or WEEKDAY() + 1 in MySQL. For the name (Monday, Tuesday…) use TO_CHAR(…, 'Day') in PostgreSQL, DAYNAME() in MySQL, and DATENAME(WEEKDAY, …) in SQL Server. Group by the weekday expression to answer "which day of the week is busiest?" — convert to one consistent time zone first, or late-night rows can land on the wrong day.

Does this work in PostgreSQL, MySQL, SQL Server, and SQLite?

Yes — this page lists the query for each dialect, since the syntax can differ between database engines.

Can I generate this query for my own tables?

Yes. Describe what you want in plain English with Forgly's free AI SQL Generator and it writes the query for your dialect.