YEAR(): function in a MySQL date and time field

From a date and time field of MySQL table we can collect the year part by using the function year(). This will collect the year in YYYY format. 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. Syntax
SELECT YEAR('2015-07-25')
Output is 2015
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 from Date column.
SELECT DATE_FORMAT('2021-08-20','%y');
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`";
echo mysql_error();
echo "<table  border='1' cellspacing='1' cellpadding='0' width='400'>
<tr valign='top'> 
</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
2004-10-26 00:00:002005-01-25200420051
2004-05-05 23:56:252005-06-12200420051
2005-12-08 13:20:102005-06-06200520050
Here is the sql code to create and fill the table with records

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',
# 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 MONTH() : Month part of the date and difference in Month


* indicates required
Subscribe to plus2net

    Post your comments , suggestion , error , requirements etc here

    SQL Video Tutorials

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 All rights reserved worldwide Privacy Policy Disclaimer