SQL PHP HTML ASP JavaScript articles and free scripts to download
SQL Date query

day function in a mysql date and time field

Day of the month can be collected from the date and time field by using the function day(). This will return day in number format and we can apply calculation to this. Note that this will not take care of month and year, for example the difference in days between 12th July 04 and 14th July 05 is two days only.

The basic query is here

SELECT day(dt2) FROM `dt_tb`

This will collect the day part from the field

For easy understanding we will create one PHP page with this query with all other fields.

Difference in Day

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

$query="SELECT dt,dt2,day(dt) as day1,day(dt2)as day2,(day( dt2 ) - day( 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>dt</b></td><td><b>dt2</b></td><td><b>day<br>(dt)</b></td>
<td><b>day<br>(dt2)</b></td><td><b>diffe-<br>rence</b></td></tr>"; while($nt=mysql_fetch_array($qt)){
echo "<tr valign='top'> <td><font face='Verdana' size='2' >$nt[dt]</font></td><td><font face='Verdana' size='2' >$nt[dt2]</font></td><td><font face='Verdana' size='2' >$nt[day1]</font></td><td><font face='Verdana' size='2' >$nt[day2]</font></td><td><font face='Verdana' size='2' >$nt[difference]</font></td></tr>";
}
echo "</table>";

The output of this query is here
dtdt2day
(dt)
day
(dt2)
diffe-
rence
2004-10-26 00:00:002005-01-252625-1
2004-05-05 23:56:252005-06-125127
2005-12-08 13:20:102005-06-0686-2
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');

Number of User Comments : 1


Google+

Naren29-02-2012
How to select a record using particular date? What is the SQL query for that?
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