DATEDIFF() function to get diferene in 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');


plus2net.com



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
















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