month() function in a mysql date 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 ( 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 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
dtdt2month1month2difference
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 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');
Visitors Rating
Your Rating




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.

Post your comments , suggestion , error , requirements etc here .










We use cookies to improve your browsing experience. . Learn more
HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
©2000-2019 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer