Difference in month from the date time field of MySQL table

SELECT MONTH(  '2016-05-05' )

Output is 5

Only month part can be collected from MySQL date and time field by using month function. This will collect the month in number format ( 1 to 12 )

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.
Syntax

MONTH(date)

DEMO: Select date from Calendar to execute MONTH()
MONTHNAME(): Month Name in Full ( September )
To get short month name as Jan, Feb ... you can use Date format.

SELECT date_format(date,'%b') as dt FROM table_name

The basic query with a MySQL table is here

SELECT month(dt2) FROM `dt_tb'

This will collect the month part from the field.

Short Month

We can search for matching short months ( three digit month name) like Jan , Feb etc using WHERE condition.

SELECT * FROM `table_name` WHERE DATE_FORMAT(date, '%b')='Jan'

Full Month name

SELECT * FROM `table_name` WHERE DATE_FORMAT( date, '%M' ) = 'January'

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 with PHP Code.

MySQLi database connection string

<?Php
require "config.php";// Database connection

$query="SELECT dt,dt2,month(dt) as month1,month(dt2)as month2,
(month( dt2 ) - month( dt )) as difference FROM `dt_tb` ";

if($stmt = $connection->query("$query")){

  echo "No of records : ".$stmt->num_rows."<br>";

  echo "<table class='table table-striped'>
<tr class='info'> <th> dt</th><th> dt2</th><th>month(dt)</th><th>month1</th><th>month2</th><th>Difference</th></tr>";
while ($row = $stmt->fetch_assoc()) {
 echo "<tr><td>$row[dt]</td><td>$row[dt2]</td><td>$row[month1]</td><td>$row[month2] </td><td>$row[difference] </td></tr>";
 }
echo "</table>";
}else{
echo $connection->error;
}
///////////////////////
?>

The output of this query is here

dt	dt2	month1	month2	difference
2004-10-26 00:00:00	2005-01-25	10	1	-9
2004-05-05 23:56:25	2005-06-12	5	6	1
2005-12-08 13:20:10	2005-06-06	12	6	-6

Here is the sql code to create and fill the table with records

CREATE TABLE IF NOT EXISTS `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`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `dt_tb`
--

INSERT INTO `dt_tb` (`id`, `dt`, `dt2`) VALUES
(1, '2004-10-26 00:00:00', '2005-01-25'),
(2, '2004-05-05 23:56:25', '2005-06-12'),
(3, '2005-12-08 13:20:10', '2005-06-06');

SQL Date References YEAR() DAY() NOW():Current Date with Time TO_DAYS() : Difference in days between two dates

Subscribe to our YouTube Channel here

John	14-09-2009
This will fail if the dates are more than a couple of years apart.

smo	15-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

Mahma	28-08-2014
Hi , I am trying to get Data from a SQL server between 2 dates,but if the month of from_date is higher than to_date it doesn't return me any records. Please help me with the same.

month() : Month part from Date column in number

Short Month

Full Month name

Difference in months

Subscribe