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

SQL PHP HTML ASP JavaScript articles and free scripts to download

Year function in a mysql data 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.

The basic query is here

SELECT year(dt2) FROM `dt_tb`

This will collect the year part from the field

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>dt</td><td>dt2</td>
<td>year (dt)</td><td>year (dt2)
</td><td>diffe- rence</td>
</tr>"; while($nt=mysql_fetch_array($qt)){
echo "<tr valign='top'> <td>$nt[dt]</td><td>$nt[dt2]
</td><td>$nt[year1]</td>
<td>$nt[year2]</td><td>$nt[difference]</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');

Discuss this tutorial at forum

List of SQL Tutorials