In which format Year(2) or Year(4) MySQL will return the value of year from date ‘0000-00-00’?

Suppose if we have stored a date value as ‘0000-00-00’ in MySQL table then on extracting year value from such kind of date, MySQL will return 0. It would not be in either Year(2) or Year(4) format. To understand it we are using the following data from ‘detail_bday’ table −

mysql> Select * from detail_bday;
+----+---------+------------+
| Sr | Name    | Birth_Date |
+----+---------+------------+
| 1 | Saurabh  | 1990-05-12 |
| 2 | Raman    | 1993-06-11 |
| 3 | Gaurav   | 1984-01-17 |
| 4 | Rahul    | 1993-06-11 |
| 5 | Sonia    | 1993-11-31 |
| 6 | Ram      | 0000-00-00 |
+----+---------+------------+
6 rows in set (0.00 sec)

Now the following query will try to fetch the year value from a date ‘0000-00-00’ −

mysql> Select Year(Birth_date) from detail_bday Where Name = 'Ram';
+------------------+
| Year(Birth_date) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

The above result set shows that MySQL returns 0 instead of giving the value in the format Year(2) or Year(4).