Difference in month from the date time field of MySQL table

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)

Example

SELECT MONTH(  '2016-05-05' )

Output is 5

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

Subscribe to our YouTube Channel here

month() function in a MySQL date and time field

Short Month

Full Month name

Difference in months

Subscribe