How to Extract Month from Date in SQL

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

Extracting the month from a date using the EXTRACT function #

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

Here’s the syntax of the EXTRACT function:

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

In this syntax,

• First, use the MONTH option to extract the month from a date before the FROM keyword.
• Second, specify the date value (date_value) from which you want to extract the month 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.

To extract the month from a table column, you use the following syntax:

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

In this syntax:

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

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

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

Try it

Output:

 the_month
-----------
         1Code 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 month from the hire_date column of the employees table:

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

Try it

Output:

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

Extracting the month from a date using the MONTH 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 MONTH function.

Here’s the syntax of the MONTH function:

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

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

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

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

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

Try it

Output:

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

Extracting the month from a date in SQLite #

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

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

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

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

Try it

Output:

 the_month
-----------
 01Code language: SQL (Structured Query Language) (sql)

The following table shows the databases and their corresponding function to extract the month from a date: