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

To extract the month and year from a specified date stored in a MySQL database is your objective.

Example:
The information in our database is stored in a table called "impression" that comprises of two columns, namely "id" and "date."

id	date
2	2023-04-28
4	2023-06-26
5	2022-04-26
6	2021-04-26

We shall retrieve the year and month values from the given date.

 

Methods 1.

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

Output:

year	month
2023	4
2023	6
2022	4
2021	4

To retrieve the year and month columns, employ the EXTRACT(part FROM date) function. In this particular method, YEAR and MONTH subsitute the part parameter to obtain and segregate the year and month into individual columns.

 

Methods 2.

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

Output:

year_and_month

202304

202104

202304

202304

The previous method and this solution operate similarly, with the exception that YEAR_MONTH retrieves both the year and month together in a solitary column rather than as distinct entities. Please note that the year and month values are not isolated from each other.

 

Methods 3.

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

Output:

year	month
2023	4
2021	4
2023	4
2023	4

In this instance, we have utilized the YEAR() and MONTH() functions to form a pair of columns. The YEAR() function furnishes the year, while the MONTH() function produces the month represented as a numerical value.

 

Methods 4.

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

Output:

year	month
2023	April
2021	April
2023	April

Use the MONTHNAME() function to obtain the name of the month. The output exhibits the name of the month rather than its corresponding number.

 

Methods 5.

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

Output:

year_and_month

2023-04

2021-04

2023-04

2023-04