Mysql Get Year From Date

mysql get year from date : Use the YEAR() function to retrieve the year value from a date/datetime/timestamp column in MySQL. YEAR() function in MySQL is used to find year from the given date.

Contents

MySQL YEAR() returns the year for a given date. The return value is in the range of 1000 to 9999 or 0 for ‘zero’ date.

How to Get the Year from a Datetime Column in MySQL?

SELECT name,
       YEAR(start_datetime) 
         AS  year_of_conference
FROM conference;

mysql query date year

SELECT book_name,join_dt,YEAR(join_dt)
FROM book_mast
WHERE YEAR(join_dt)>2003;

Don’t Miss : Laravel Get Last 7 Days Month Year Record From MySQL

By using EXTRACT() function

mysql> Select EXTRACT(YEAR_MONTH From estb) from MembersInformation;
+-------------------------------+
| EXTRACT(YEAR_MONTH From estb) |
+-------------------------------+
|                        212105 |
|                        203010 |
|                        202209 |
|                        201407 |
|                        202507 |
+-------------------------------+
5 rows in set (0.00 sec)

By using DATE_FORMAT() function

mysql> Select DATE_FORMAT(estb, '%Y %m') from MembersInformation;
+----------------------------+
| DATE_FORMAT(estb, '%Y %m') |
+----------------------------+
| 2021 05                    |
| 2022 10                    |
| 2023 09                    |
| 2030 07                    |
| 2021 07                    |
+----------------------------+
5 rows in set (0.00 sec)

mysql> Select DATE_FORMAT(estb, '%Y') from MembersInformation;
+-------------------------+
| DATE_FORMAT(estb, '%Y') |
+-------------------------+
| 2021                    |
| 2022                    |
| 2023                    |
| 2030                    |
| 2021                    |
+-------------------------+
5 rows in set (0.00 sec)

mysql> Select DATE_FORMAT(estb, '%m') from MembersInformation;
+-------------------------+
| DATE_FORMAT(estb, '%m') |
+-------------------------+
| 02                      |
| 11                      |
| 12                      |
| 04                      |
| 03                      |
+-------------------------+
5 rows in set (0.00 sec)

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

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

mysql> Select YEAR(estb) AS 'Year', MONTH(estb) As 'MONTH' From collegedetail;
+------+-------+
| Year | MONTH |
+------+-------+
| 2021 |     5 |
| 2022 |    10 |
| 2025 |     9 |
| 2028 |     7 |
| 2021 |     7 |
+------+-------+
5 rows in set (0.00 sec)

Example

SELECT 
    DATE_FORMAT(date_registered, '%M, %y') AS year_month_registered, 
    COUNT(*) AS num_registered
FROM `members`
GROUP BY year_month_registered

I hope you get an idea about mysql get year from date.
I would like to have feedback on my infinityknow.com.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

mysql get year from date – 5 ways to Get the Year from a Datetime Column in MySQL