MySQL DATEDIFF(): Difference Between Two Dates

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
If either argument is NULL, the result is NULL. Invalid date handling can also depend on SQL mode, so avoid relying on invalid literals.

SELECT DATEDIFF('2016-02-15', NULL);

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 UNSIGNED NOT NULL AUTO_INCREMENT,
    dt DATETIME NOT NULL,
    dt2 DATE NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO dt_tb (dt, dt2) VALUES
('2004-10-26 00:00:00', '2005-01-25'),
('2004-05-05 23:56:25', '2005-06-12'),
('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

All Date & Time Functions Date differences

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

DATEDIFF() function to get differene in days

Subhendu Mohapatra