DATEDIFF() function to get differene in days

We can get difference between two dates in number of days.
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

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

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


* indicates required
Subscribe to plus2net

    Post your comments , suggestion , error , requirements etc here .

    SQL Video Tutorials

    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 All rights reserved worldwide Privacy Policy Disclaimer