SELECT YEAR('2015-07-25')
Output is 2015
SELECT YEAR('2015-02-31')
Output is NULL
SELECT year(dt2) FROM `dt_tb`
This will collect the year part from the field
SELECT DATE_FORMAT('2021-08-20','%y');
Output
21
For easy understanding we will create one PHP page with this query with all other fields.
$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 |
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 ReferencesMONTH()
DAY()
NOW() : Current Date with Time