Back to cookbooks list Articles Cookbook

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.

iddate
12008-04-21
21987-12-14

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:

yearmonth
20084
198712

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:

year_and_month
200804
198712

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:

yearmonth
20084
198712

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:

yearmonth
2008April
1987December

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:

year_and_month
2008-04
1987-12

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.

Recommended courses:

Recommended articles:

See also: