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 monthFROM 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_monthFROM 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 monthFROM 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 monthFROM 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_monthFROM 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.