How can we extract year and month from date in MySQL?

It can be done in the following three ways in MySQL

By using the EXTRACT() function

Extract YEAR and MONTH together and then we You can use the EXTRACT function. We need to provide YEAR_MONTH as argument to this function. To understand it, consider the following function using data from table "Collegedetail" -

mysql> Select EXTRACT(YEAR_MONTH From estb) from collegedetail;
+-------------------------------+
| EXTRACT(YEAR_MONTH From estb) |
+-------------------------------+
|                        201005 |
|                        199510 |
|                        199409 |
|                        200107 |
|                        201007 |
+-------------------------------+
5 rows in set (0.00 sec)

The year and month can be extracted either collectively or individually by using the DATE_FORMAT() function

. As the name suggests, we can also format its output. To understand it, consider the following example which uses data from table "Collegedetail" -

mysql> Select DATE_FORMAT(estb, '%Y %m') from collegedetail;
+----------------------------+
| DATE_FORMAT(estb, '%Y %m') |
+----------------------------+
| 2010 05                    |
| 1995 10                    |
| 1994 09                    |
| 2001 07                    |
| 2010 07                    |
+----------------------------+
5 rows in set (0.00 sec)

mysql> Select DATE_FORMAT(estb, '%Y') from Collegedetail;
+-------------------------+
| DATE_FORMAT(estb, '%Y') |
+-------------------------+
| 2010                    |
| 1995                    |
| 1994                    |
| 2001                    |
| 2010                    |
+-------------------------+
5 rows in set (0.00 sec)

mysql> Select DATE_FORMAT(estb, '%m') from Collegedetail;
+-------------------------+
| DATE_FORMAT(estb, '%m') |
+-------------------------+
| 05                      |
| 10                      |
| 09                      |
| 07                      |
| 07                      |
+-------------------------+
5 rows in set (0.00 sec)

mysql> Select DATE_FORMAT(estb, '%M') from Collegedetail;
+-------------------------+
| DATE_FORMAT(estb, '%M') |
+-------------------------+
| May                     |
| October                 |
| September               |
| July                    |
| July                    |
+-------------------------+
5 rows in set (0.10 sec)

By using two different functions YEAR() and MONTH()

it will use Two different functions extract the year and month respectively. To understand it, consider the following example, which uses data from table "Collegedetail" -

mysql> Select YEAR(estb) AS 'Year', MONTH(estb) As 'MONTH' From collegedetail;
+------+-------+
| Year | MONTH |
+------+-------+
| 2010 |     5 |
| 1995 |    10 |
| 1994 |     9 |
| 2001 |     7 |
| 2010 |     7 |
+------+-------+
5 rows in set (0.00 sec)

The above is the detailed content of How can we extract year and month from date in MySQL?. For more information, please follow other related articles on the PHP Chinese website!