DATEDIFF() MySQL function to get differene between two dates in number of days

We can get difference between two dates in number of days.
Syntax

DATEDIFF(date1,date2)

Example

SELECT DATEDIFF('2016-03-24','2015-02-28');

Output is 390
For a invalid date we will get Null as output

SELECT DATEDIFF('2016-02-15','2015-12-32')

Output is NULL The basic query with MYSQL table is here

SELECT id,dt,dt2, DATEDIFF(dt2,dt) as diff FROM dt_tb

This will return all the records with difference between dt2 and dt in days. Here is the output

id	dt	dt2	diff
1	2004-10-26 00:00:00	2005-01-25	91
2	2004-05-05 23:56:25	2005-06-12	403
3	2005-12-08 13:20:10	2005-06-06	-185
4	2003-05-26 00:00:00	2007-12-18	1667
5	2007-12-18 00:00:00	2003-08-16	-1585

You can display above table by using any Sever side script like PHP, JSP , ASP, .NET etc.

PHP Script

config.php connection string is available here

PDO for executing Queries in PHP

How to get records using FETCH() are here

<?Php
require 'config.php';  // Database connection string
$query='SELECT id,dt,dt2, DATEDIFF(dt2,dt) as diff FROM dt_tb';

echo "<table><tr ><th>id</th><th>dt</th><th>dt2</th><th>diff</th></tr>";

foreach ($dbo->query($query) as $row) {
echo "<tr><td>$row[id]</td><td>$row[dt]</td><td>$row[dt2]</td><td>$row[diff]</td></tr>";
}

echo "</table>";
?>

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');

Subscribe to our YouTube Channel here

DATEDIFF() function to get differene in days

Subscribe