Fetch the data from my MySQL database for the current month

Question

I want to fetch the data from my MySQL database for the current month.

In the database, I have a column next_due which stores the next month date.

Now, if suppose this column is 13-Nov-2020, and today is 13-Nov-2020, code should show the data of those person whose due is on 13-Nov-2020.

Here is my query:

SELECT * FROM new_regis WHERE MONTH(next_due) = MONTH(CURRENT_DATE())
AND YEAR(next_due) = YEAR(CURRENT_DATE())

But this is not working.

using a real timestamp column with a properly formatted timestamp would likely make this simpler. — Wesley Smith, Nov 15 '20 at 11:13

score 2 · Accepted Answer · edited Nov 22 '20 at 14:17

You really should store dates in columns of type DATE. This would make your life a lot simpler.

Fortunately, MySQL has built-in functions to help you work with dates that are stored as text. MONTH(next_due) won't work, because MySQL doesn't know how to parse next_due as a date value. You'll have to tell it how first: MONTH( STR_TO_DATE(next_due, '%d-%b-%Y') ).

The STR_TO_DATE function will parse a string into a date, given a format specifier of how to parse the string. That %d-%b-%Y format specifier should match your format if your days are two digits. If you store dates as 5-Nov-2020 (with a single digit for the day) use %e instead of %d. The full list of options for your format are listed with the DATE_FORMAT function in the documentation.

So the following query should work for your purposes:

SELECT * FROM new_regis WHERE MONTH(STR_TO_DATE(next_due, '%d-%b-%Y')) = MONTH(CURRENT_DATE())
AND YEAR(STR_TO_DATE(next_due, '%d-%b-%Y')) = YEAR(CURRENT_DATE())

The question only needed guidance with `STR_TO_DATE()`. There are several pages where this advice exists on Stack Overflow. Please help us to close redundant questions. Thanks. — mickmackusa, Nov 22 '20 at 10:49

B_Sharp · Answer 2 · 2020-11-22T09:53:30.263

I think is better to deal with Timestamp only in all the way.

Store the next_due filed as timestamp.
$today=time().
Fetch from the database and compare.



$sql = "SELECT here";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
  // output data of each row

  while($row = $result->fetch_assoc()) 
{
$next_due=$row['next_due'];
$today=time()
$data=array();
if ($next_due==$today)
{
//True show data here
$data['id']=$row['id'];
$data['name']=$row['name'];

print_r"$data";

}

else
{
//False
print"Sorry not current day.";
}

  }
}
 else {
  echo "0 results";
}

score -3 · Answer 3 · edited Nov 22 '20 at 14:15

-3

To get current month data you can use

SELECT * FROM users WHERE MONTH(2020-05-15 17:36:38) = 5

Here 5 is the current month that you can get from PHP

edited Nov 22 '20 at 14:15

Dharman

30,962
25
85
135

answered Nov 15 '20 at 11:22

SK Shewa

106
1
9

Why minus vote! Minus vote just discourage to help – SK Shewa Nov 15 '20 at 11:30
I suspect someone downvoted because the query won't work (quotes are missing) and also the person asking the question has a different date format this won't work for either. Compare your answer to the accepted one that clearly shows how to work with that format. – PeterJ Nov 15 '20 at 11:58
i was just give him an idea , and on the first he wrote: i want to fetch current month data. Quotation i did not use and is clearly visible, one developer can easily understand this. – SK Shewa Nov 15 '20 at 12:06
@SKS you can choose to leave your answer here or remove it. Either way, this redundant page is likely to be deleted entirely. – mickmackusa Nov 22 '20 at 10:47

Fetch the data from my MySQL database for the current month

3 Answers3