month() : Month part from Date column in number

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

SQL Date References YEAR() DAY() NOW():Current Date with Time TO_DAYS() : Difference in days between two dates
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    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





    SQL Video Tutorials










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