SQL Standard

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:

SELECT MONTH(date_column) AS extracted_month
FROM table_name;

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:

SELECT MONTH('2024-01-08') AS jan;

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

jan|
---+
  1|

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

select title, MONTH(last_update) from film limit 3;

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:

SELECT DATE_FORMAT(date_column, '%m') AS extracted_month
FROM table_name;

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:

SELECT DATE_FORMAT('2024-01-08', '%m') AS jan;

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

jan|
---+
01 |

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:

SELECT EXTRACT(MONTH FROM date_column) AS extracted_month
FROM table name;

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

An example usage of this function is as follows:

SELECT EXTRACT(MONTH FROM '2024-01-08'::date) AS jan;

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:

SELECT DATE_PART('month', date_column) AS extracted_month
FROM table name;

An example usage is as follows:

SELECT DATE_PART('month', '2024-01-08'::date) AS jan;

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.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list