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
DEMO: Select two dates and find difference in days by using DATEDIFF()
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');

SQL Date References Difference in days between two date fields Storing date in MySQL date field

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

DATEDIFF() function to get differene in days

Subhendu Mohapatra