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

month function in a mysql data and time field

Only month part can be collected from mysql date and time field by using month function. This will collect the month in number format and please note that it will not take care of year part. So if you are taking the difference in month for July 2004 and August 2005 then you will get a difference of one.


The basic query is here

SELECT month(dt2) FROM `dt_tb'

This will collect the month part from the field For easy understanding we will create one PHP page with this query with all other fields.

Difference in months

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

$query="SELECT dt,dt2,month(dt) as month1,month(dt2)as month2,(month( dt2 ) - month( 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>month<br>(dt)</b></td
><td><b>month<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[month1]</font></td><td><font face='Verdana' size='2' >$nt[month2]</font></td><td><font face='Verdana' size='2' >$nt[difference]</font></td></tr>";
}
echo "</table>";


The output of this query is here
dtdt2month
(dt)
month
(dt2)
diffe-
rence
2004-10-26 00:00:002005-01-25101-9
2004-05-05 23:56:252005-06-12561
2005-12-08 13:20:102005-06-06126-6


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 : 2


Google+

John14-09-2009
This will fail if the dates are more than a couple of years apart.
smo15-09-2009
You can see from the above table that this only gives the numeric difference of the month values without considering the years. So it is not expected to give you correct difference in months
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