SQL PHP HTML ASP JavaScript articles and free scripts to download

Difference in days between two date fields of a record

Difference in date between two date columns in a table can be found out by converting the columns by using to_days function. After converting that can be simply subtracted to get the difference.

Example of such a application is to find out the days a guest has stayed in a hotel, we have to take the difference of arrival date and departure date. Both the fields are date and time fields. Another example is in library if we are finding out the difference in days between date of issue and date of return. You can get many such applications where difference in days are required. Here is the sql query applied to a mysql table and the result is shown. You can get the sql dump of the table at the end of this tutorial.

The basic query is here

SELECT To_days( dt2 ) - TO_DAYS( dt ) FROM `dt_tb`

We will create one PHP page with this query for easy understanding of the application.

The sql query is modified to display all the columns with the day difference for easy comparison. Here is the query used.

$query="SELECT id,dt,dt2,(To_days( dt2 ) - TO_DAYS( dt )) as difference FROM `dt_tb`";

$qt=mysql_query($query);
echo mysql_error();
echo "<table border='1' cellspacing='1' cellpadding='0' width='400'>
<tr valign='top'>
<td><b>id</b></td><td><b>dt</b></td><td><b>dt2</b></td>
<td><b>difference</b></td></tr>";
while($nt=mysql_fetch_array($qt)){
echo "<tr valign='top'> <td>$nt[id]</td><td>$nt[dt]</td><td>$nt[dt2]</td>
<td>$nt[difference]</td></tr>";
}
echo "</table>";


The output of this query is here
iddtdt2difference
12004-10-26 00:00:002005-01-2591
22004-05-05 23:56:252005-06-12403
32005-12-08 13:20:102005-06-06-185


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


Google+

Tamilselvi.k12-03-2009
query for find orderdate ,15 days after today's date from sales_order table.
mishal16-12-2009
select Sysdate 15 from dual;

this is useful to find 15 days after today's date.
Post Comment This is for short comments only. Use the forum for more discussions.
Name
Email( not to be displayed)Privacy Policy
1+2=This is to prevent automatic submission by spammers. Please enter the result of the sum as asked



Join Our Email List
Email:  
For Email Newsletters you can trust

HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

©2000-2014 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer