How to Get the Year and the Month From a Date in MySQL
Database:
Operators:
Table of Contents
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
.
id | date |
---|---|
1 | 2008-04-21 |
2 | 1987-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:
year | month |
---|---|
2008 | 4 |
1987 | 12 |
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:
year | month |
---|---|
2008 | 4 |
1987 | 12 |
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:
year | month |
---|---|
2008 | April |
1987 | December |
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.