How to Get the Year and the Month From a Date in MySQL

Problem:

You want to get the year and the month from a given date in a MySQL database.

Example:

Our database has a table named dates with data in the columns id and date.

Let’s extract the year and the month from the date.

Solution 1:

SELECT
  EXTRACT(YEAR FROM date) AS year,
  EXTRACT(MONTH FROM date) AS month
FROM dates;

The result is:

Discussion:

To get the year and the month columns, use the EXTRACT(part FROM date) function. In this solution, the part argument is replaced by YEAR and MONTH to get the year and the month separately, each in its own column.

You can learn more about EXTRACT() in the official MySQL documentation.

Solution 2:

SELECT
  EXTRACT(YEAR_MONTH FROM date) AS year_and_month
FROM dates;

The result is:

Discussion:

This solution works exactly like the previous one, but YEAR_MONTH is used to get the year and the month together in one column instead of getting them separately. Notice that the values of the year and the month are not separated from each other.

Solution 3:

SELECT
  YEAR(date) AS year,
  MONTH(date) AS month
FROM dates;

The result is:

Discussion:

This time, the YEAR() and the MONTH() functions are used to create two columns. YEAR() returns the year and MONTH() returns the month as a number.

Solution 4:

SELECT
  YEAR(date) AS year,
  MONTHNAME(date) AS month
FROM dates;

The result is:

Discussion:

To get the name of the month, use the MONTHNAME() function. The result displays the month name instead of the month number.

Solution 5:

SELECT
  DATE_FORMAT(date, '%Y-%m') AS year_and_month
FROM dates;

The result is:

Discussion:

Use the DATE_FORMAT() function to display date values in a specific format. It takes the date as the first argument and a string describing the desired date format as the second argument. In our case, the string '%Y-%m', %Y returns the year, ‘-’ is used as a separator, and %m returns the month numerically (it can be replaced by %M to get the month name).

You can learn more about DATE_FORMAT() in the official MySQL documentation.