SELECT MONTH( '2016-05-05' )
Output is 5
MONTH(date)
DEMO: Select date from Calendar to execute MONTH()
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.
SELECT * FROM `table_name` WHERE DATE_FORMAT(date, '%b')='Jan'
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.
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;
}
///////////////////////
?>
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 |
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');
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. |