How to Extract Day from Date in SQL

Summary: in this tutorial, you’ll learn how to extract a day from a date value or column in SQL.

Extracting the day from a date using the EXTRACT function #

SQL provides you with the EXTRACT function that allows you to extract the day from a date value.

Here’s the syntax of the EXTRACT function:

EXTRACT(DAY FROM date_value)Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, use the DAY option to extract the day from a date before the FROM keyword.
• Second, specify the date value (date_value) from which you want to extract the day after the FROM keyword.

If the date_value is not a value of the DATE type, you may need to explicitly convert it to a value of the DATE type before passing it to the EXTRACT function.

The following shows the syntax of using the EXTRACT function to extract the day from a table column:

SELECT EXTRACT(DAY FROM date_column)
FROM table_name;Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the DAY option before the FROM keyword to instruct the EXTRACT function to return the day.
• Second, pass the date_column to the EXTRACT function after the FROM keyword.

The following statement uses the EXTRACT function to extract the day from a date value:

SELECT EXTRACT(DAY FROM '2025-01-19'::DATE) the_day;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 the_day
---------
      19Code language: SQL (Structured Query Language) (sql)

In this example, we use the cast operator (::) to cast a string to a value of the DATE type in PostgreSQL.

The following example uses the EXTRACT function to extract the day from the hire_date column of the employees table:

SELECT
  first_name,
  hire_date,
  EXTRACT(day FROM hire_date) joined_day
FROM
  employees
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  | hire_date  | joined_day
-------------+------------+------------
 Adam        | 1997-04-10 |         10
 Alexander   | 1990-01-03 |          3
 Alexander   | 1995-05-18 |         18
 Britney     | 1997-03-03 |          3
 Bruce       | 1991-05-21 |         21
 Charles     | 2000-01-04 |          4Code language: SQL (Structured Query Language) (sql)

Extracting the day from a date using the DAY function #

Many database systems, including PostgreSQL, Oracle, and Firebird, widely support the EXTRACT function.

However, database systems like MySQL, MariaDB, SQL Server, Db2, SAP HANA, Informix, and Teradata do not support the EXTRACT function. Instead, they offer the DAY() function that allows you to extract the day from a date value.

Here’s the syntax of the DAY function:

DAY(date_value)Code language: SQL (Structured Query Language) (sql)

You can also use a date column for the DAY function:

SELECT
  DAY(date_column)
FROM
  table_name;Code language: SQL (Structured Query Language) (sql)

For example, you can extract the day from a date in MySQL using the day function as follows:

SELECT
  first_name,
  hire_date,
  DAY (hire_date) joined_day
FROM
  employees
ORDER BY
  first_name;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 first_name  | hire_date  | joined_day
-------------+------------+------------
 Adam        | 1997-04-10 |         10
 Alexander   | 1990-01-03 |          3
 Alexander   | 1995-05-18 |         18
 Britney     | 1997-03-03 |          3
 Bruce       | 1991-05-21 |         21
...Code language: SQL (Structured Query Language) (sql)

Extracting the day from a date in SQLite #

SQLite does not support both EXTRACT and DAY functions. Instead, it offers the strftime() function that you can format a date to a day using the '%d' format string:

strftime('%d', date_column)Code language: SQL (Structured Query Language) (sql)

For example, you can extract the day from a date using the strftime function as follows:

SELECT
  strftime ('%d', '2025-01-19') the_day;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 the_day
---------
 19Code language: SQL (Structured Query Language) (sql)

The following table shows the database systems and their corresponding function to extract the day from a date: