SQL PHP HTML ASP JavaScript articles and free scripts to download
 

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.

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




Further readings
Date & Time functions used in Query for MySQL Table
Getting formatted date value from date field in MySQL
date_add function to calculate date & time of MySQL table
Formatting date and time before adding to date field of MySQL
String data to Date & time Format by using str_to_date
Formatting string data stored in varchar field to date value
Automatically updating / inserting current date and time value in a DATETIME field
Records of each day by using group by command
Collecting records between two date ranges from MySQL table fields
Getting date values from MYSQL table in readable format including time
Difference in days between two date fields
Getting the year part from date field
Getting the month part from date field
Getting the day part from date field
Records of today or records of last X seconds by using curdate() or unix_timestamp
Records of last one month from today by using date field
Records of present week days by using dayofweek function
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
Date & Time
HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us

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