Select mySQL based only on month and year

I have a column in my mySQL DB that has some rows. One of this row is a DATE, like this: 2012-02-01

What I want to achieve is to do a SELECT with PHP based only on the year and month.

The logic of the SELECT will be the following:

$q="SELECT * FROM projects WHERE Date="SELECT HERE THE SPECIFIC YEAR AND MONTH"";

The specific month and year will be be passed from a $_POST variable, like this $_POST['period']="2012-02";

How can I do it?

SELECT * FROM projects WHERE YEAR(Date) = 2011 AND MONTH(Date) = 5

If you have

$_POST['period'] = "2012-02";

First, find the first day of the month:

$first_day = $_POST['period'] . "-01"

Then this query will use an index on Date if you have one:

$q = "
    SELECT *  
    FROM projects 
    WHERE Date BETWEEN '$first_day' 
                   AND LAST_DAY( '$first_day' )
     " ;

One could also use inclusive-exclusive intervals, which work pretty good (you don't have to worry if the column is DATE, DATETIME or TIMESTAMP, nor about the precision:

$q = "
    SELECT *  
    FROM projects 
    WHERE Date >= '$first_day' 
      AND Date  < '$first_day' + INTERVAL 1 MONTH 
     " ;

---

Security warning:

You should properly escape these values or use prepared statements. In short, use whatever method is recommended these days in PHP, to avoid any SQL injection issues.

Here you go. Leave the computing to PHP and save your DB some work. This way you can make effective use of an index on the Date column.

<?php
    $date = $_POST['period'];

    $start = strtotime($date);
    $end   = strtotime($date . ' 1 month - 1 second');

    $query = sprintf(
        'SELECT *
           FROM projects
          WHERE Date BETWEEN FROM_UNIXTIME(%u) AND FROM_UNIXTIME(%u)',
        $start,
        $end
    );

EDIT
Forgot the Unix timestamp conversion.

Suppose you have a database field created_at Where you take value from timestamp. You want to search by Year & Month from created_at date.

YEAR(date(created_at))=2019 AND MONTH(date(created_at))=2

$q="SELECT * FROM projects WHERE YEAR(date) = 2012 AND MONTH(date) = 1;

Here, FIND record by MONTH and DATE in mySQL

Here is your POST value $_POST['period']="2012-02";

Just, explode value by dash $Period = explode('-',$_POST['period']);

Get array from explode value :

Array ( [0] => 2012 [1] => 02 )

Put value in SQL Query:

SELECT * FROM projects WHERE YEAR(Date) = '".$Period[0]."' AND Month(Date) = '".$Period[0]."';

Get Result by MONTH and YEAR.

The logic will be:

SELECT * FROM objects WHERE Date LIKE '$_POST[period]-%';

The LIKE operator will select all rows that start with $_POST['period'] followed by dash and the day of the mont

http://dev.mysql.com/doc/refman/5.0/en/pattern-matching.html - Some additional information

You can do like this:

$q="SELECT * FROM projects WHERE Year(Date) = '$year' and Month(Date) = '$month'";

to get the month and year values from the date column

select year(Date) as "year", month(Date) as "month" from Projects

You can use mysql DATE_FORMAT function consider just the year+month part of the date:

select * from table where DATE_FORMAT(column, '%Y%m') = '202105';

you can do it by changing $q to this:

$q="SELECT * FROM projects WHERE YEAR(date) = $year_v AND MONTH(date) = $month_v;

No one seems to be talking about performance so I tested the two most popular answers on a sample database from Mysql. The table has 2.8M rows with structure

CREATE TABLE `salaries` (
  `emp_no` int(11) NOT NULL,
  `salary` int(11) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `salaries`
  ADD PRIMARY KEY (`emp_no`,`from_date`);

Here are the tests performed and results

SELECT * FROM `salaries` WHERE YEAR(from_date) = 1992 AND MONTH(from_date) = 6
12339 results
-----------------
11.5 ms 
12.1 ms
09.5 ms
07.5 ms
10.2 ms
-----------------
10.2 ms Avg
-----------------


SELECT * FROM `salaries` WHERE from_date BETWEEN '1992-06-01' AND '1992-06-31'
-----------------
10.0 ms
10.8 ms
09.5 ms
09.0 ms
08.3 ms
-----------------
09.5 ms Avg
-----------------


SELECT * FROM `salaries` WHERE YEAR(to_date) = 1992 AND MONTH(to_date) = 6
10887 results
-----------------
10.2 ms
11.7 ms
11.8 ms
12.4 ms
09.6 ms
-----------------
11.1 ms Avg
-----------------


SELECT * FROM `salaries` WHERE to_date BETWEEN '1992-06-01' AND '1992-06-31'
-----------------
09.0 ms
07.5 ms
10.6 ms
11.7 ms
12.0 ms
-----------------
10.2 ms Avg
-----------------

My Conclusions

1. BETWEEN was slightly better on both indexed and unindexed column.
2. The unindexed column was marginally slower than the indexed column.

To get the specific month and year data from the table

SELECT * FROM table WHERE DATE_FORMAT(column_name,'%Y-%m') = '2021-06'

Here is a query that I use and it will return each record within a period as a sum.

Here is the code:

$result = mysqli_query($conn,"SELECT emp_nr, SUM(az) 
FROM az_konto 
WHERE date BETWEEN '2018-01-01 00:00:00' AND '2018-01-31 23:59:59' 
GROUP BY emp_nr ASC");

echo "<table border='1'>
<tr>
<th>Mitarbeiter NR</th>
<th>Stunden im Monat</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
$emp_nr=$row['emp_nr'];
$az=$row['SUM(az)'];
echo "<tr>";
echo "<td>" . $emp_nr . "</td>";
echo "<td>" . $az . "</td>";
echo "</tr>";
}
echo "</table>";
$conn->close();
?>

This lists each emp_nr and the sum of the monthly hours that they have accumulated.

Yeah this is working, but it returns one row only while there is several rows to retrieve using selected columns only. Like SELECT Title,Date FROM mytable WHERE YEAR(Date)=2017 AND MONTH(Date)=09 returns only one row.