Get the Month from a Given Date in SQL

When working in SQL databases, you might encounter such instances where you need to extract specific parts of a date. For example, you may need to extract which day, month, or year a specific row contains.

In this tutorial, we will explore all the methods and techniques that you can use to extract the month from a given date in various SQL databases.

MySQL/SQL Server

Let us start with one of the most popular and powerful SQL databases – MySQL. In MySQL, there are various methods that we can use to extract the month from a given date.

Method 1: Month() Function
MySQL provides us with a very convenient method that we can use to extract the month from a given date.

The syntax of the month() function is as follows:

In this case, we specify the column that contains the date values. We can also specify a date literal or date expression.

Consider the following example that demonstrates how to use this function with a date literal:

This should return the month from the specified date as an integer value. An example output is as follows:

We can also accomplish the same on a date column as shown in the following:

The given query should extract the month from the “last_update” column as shown in the resulting output.

NOTE: This method should work pretty much the same in SQL Server databases.

Method 2: Using the Date_Format() Function
In MySQL, we have access to the date_format() method which allows us to format the date values into a specific format.

We can also use this function to extract the month from the date using the “%m” specifier as shown in the following syntax:

Similar to the month() function, we can specify a date literal, a date column, or a date expression.

Consider an example as shown in the following:

This should return the month from the specified date as shown in the following output:

PostgreSQL

Let us move on to PostgreSQL. Like MySQL, we have a few methods and techniques that we can use to extract the month from a given date.

Method 1: Extract()
In PostgreSQL, we have a conveniently named function called extract() that allows us to extract specific date parts from a given date value.

We can use this function to extract the month from a given date. The function syntax is as follows:

We can also specify a date literal, a date column, or a date expression.

An example usage of this function is as follows:

This query should return the month value from the date as a numerical value.

Method 2: Date_Part()
Yes, there is a function called date_part() in PostgreSQL. We can use it to quickly extract the month from a specified date.

The function syntax is as expressed in the following code snippet:

An example usage is as follows:

This should return the month value from the specified date.

Conclusion

In this tutorial, we learned about various methods and functions that we can use to extract the month and other date parts from a given date value.