MySQL: Extract Year and Month from Date column.

This is a short guide on how to get the year and month from a date column in MySQL. To do this, we will use the EXTRACT function, which allows us to extract parts of a date.

Below, you will find a screenshot of a MySQL table called “members“:

As you can see, the table has three columns:

1. The Primary Key id.
2. The name of the member.
3. A date column called date_registered.

Let’s say that we want to count how many members signed up and group them by the year and month that they registered? In this case, we could use the EXTRACT function to get the Year and Month from the date_registered column.

SELECT 
    EXTRACT(YEAR_MONTH FROM date_registered) AS year_month_registered, 
    COUNT(*) AS num_registered
FROM `members`
GROUP BY year_month_registered

If you run the SQL query above, you will see that it returns the following result:

As you can see, the Year and Month has been stored in a YYYYMM format in an alias called “year_month_registered.”

Using DATE_FORMAT to get the year and month.

Another alternative is to use the DATE_FORMAT function. This can be particularly useful if you are looking for a specific format. For example, if you want a hyphen to separate the year and the month, then you can use the following:

SELECT 
    DATE_FORMAT(date_registered, '%Y-%m') AS year_month_registered, 
    COUNT(*) AS num_registered
FROM `members`
GROUP BY year_month_registered

The query above will result in the following format:

You can even the tell the DATE_FORMAT function that you want MySQL to return the full textual name of the month:

SELECT 
    DATE_FORMAT(date_registered, '%M, %y') AS year_month_registered, 
    COUNT(*) AS num_registered
FROM `members`
GROUP BY year_month_registered

The MySQL query above will return the month name and the year, separated by a comma:

Hopefully, you found these examples to be helpful!