YEAR() Getting the year part of date & finding Difference in Year of the date time field

SELECT YEAR('2015-07-25')

Output is 2015

We can even calculate the year difference between two columns. Here is the main query to get the year part from MySQL date and time field.
For a invalid date we will get Null as output

SELECT YEAR('2015-02-31')

Output is NULL
DEMO: Select date from Calendar to execute YEAR()
The basic query with MYSQL table is here.

SELECT year(dt2) FROM `dt_tb`

This will collect the year part from the field

Two digit Year or YY format from Date column.

SELECT DATE_FORMAT('2021-08-20','%y');

Output

For easy understanding we will create one PHP page with this query with all other fields.

The sql query is modified to display all the columns with the years and their difference for easy comparison. Here is the query used.

$query="SELECT dt,dt2,year(dt) as year1,year(dt2)as year2,(year( dt2 ) - year( dt )) as difference FROM `dt_tb`";
$qt=mysql_query($query);
echo mysql_error();
echo "<table  border='1' cellspacing='1' cellpadding='0' width='400'>
<tr valign='top'> 
<td><b>dt</b></td><td><b>dt2</b></td>
<td><b>year<br>(dt)</b></td><td><b>year<br>(dt2)</b>
</td><td><b>diffe-<br>rence</b></td>
</tr>";
while($nt=mysql_fetch_array($qt)){
echo "<tr valign='top'> <td><font face='Verdana' size='2' >$nt[dt]</font></td><td><font face='Verdana' size='2' >$nt[dt2]</font>
</td><td><font face='Verdana' size='2' >$nt[year1]</font></td>
<td><font face='Verdana' size='2' >$nt[year2]</font></td><td><font face='Verdana' size='2' >$nt[difference]</font></td></tr>";
}
echo "</table>";

The output of this query is here

dt	dt2	year (dt)	year (dt2)	diffe- rence
2004-10-26 00:00:00	2005-01-25	2004	2005	1
2004-05-05 23:56:25	2005-06-12	2004	2005	1
2005-12-08 13:20:10	2005-06-06	2005	2005	0

Here is the sql code to create and fill the table with records

CREATE TABLE dt_tb (
id int(2) NOT NULL auto_increment,
dt datetime NOT NULL default '0000-00-00 00:00:00',
dt2 date NOT NULL default '0000-00-00',
PRIMARY KEY (id)
) TYPE=MyISAM;
#
# Dumping data for table `dt_tb`
#
INSERT INTO dt_tb VALUES (1, '2004-10-26 00:00:00', '2005-01-25');
INSERT INTO dt_tb VALUES (2, '2004-05-05 23:56:25', '2005-06-12');
INSERT INTO dt_tb VALUES (3, '2005-12-08 13:20:10', '2005-06-06');

SQL Date References MONTH() DAY() NOW() : Current Date with Time

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

YEAR(): Year Part from Date column in YYYY format

Subhendu Mohapatra