How to Extract Month from Date in SQL

Summary: in this tutorial, you will learn how to extract the month from a date in SQL by using some date functions.

To extract the month from a particular date, you use the EXTRACT() function. The following shows the syntax:

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

In this syntax, you pass the date from which you want to extract the month to the EXTRACT() function. The date can be a date literal or an expression that evaluates to a date value.

The EXTRACT() function returns a number which represents the month of the date.

The following example illustrates how to extract the month from the date of August 1st 2018:

SELECT EXTRACT(MONTH FROM '2018-08-01')
Code language: SQL (Structured Query Language) (sql)

The result is 8 as we expected:

8
Code language: SQL (Structured Query Language) (sql)

To get the current month, you use the CURRENT_TIMESTAMP function and pass it to the EXTRACT()function as follows:

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

The EXTRACT() function is a SQL standard function supported by MySQL, Oracle, and PostgreSQL.

If you use SQL Server, you can use the MONTH() or DATEPART() function to extract the month from a date.

For example, the following statement returns the current month in SQL Server:

SELECT MONTH(CURRENT_TIMESTAMP);

SELECT DATEPART(month, CURRENT_TIMESTAMP);
Code language: SQL (Structured Query Language) (sql)

Similar to SQL Server, MySQL also supports the MONTH() function to return the month from a date.

SQLite does not support EXTRACT(), MONTH() or DATEPART() function. To extract a month from a date, you use the strftime() function as follows:

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

In this tutorial, you have learned how to use various functions to extract the month from a date in SQL.

Was this tutorial helpful ?