0

I am saving the date in 03/20/2015 format in mysql table, Now I want to get all the rows with the current month so what will be the query which is fast. Thanks a ton for the help and i am sorry for the bad English.

Deepesh
  • 45
  • 1
  • 10

4 Answers4

6

You have to use this below query

$sql = "SELECT * FROM table WHERE month(date_format($date, 'm/d/Y')) = EXTRACT(month FROM (NOW()))";
$result = query($sql);
while ($row = mysql_fetch_assoc($result)) {
echo $row['fieldname'];}
Bhaveshkumar Patel
  • 172
  • 1
  • 5
4

For example you are saving date in date_entered column of table. So to get all the records in which the date_entered has current month you can use below SQL Query.

$Query = "SELECT * FROM WHERE MONTH(date_entered) = MONTH(CURDATE())";

Aniruddh Vaghasia
  • 41
  • 1
2

You should never store the dates as varchar string, it should be stored with one of mysql's native data type.

However in your case you can do something as

select * from table_name
where
month(str_to_date(date_col,'%m/%d/%Y')) = month(curdate())
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
  • I am taking date in this format "03/20/2015" and saving it. So can you please explain how to convert this date format to mysql native date type so that i use the right way to solve the problem ? – Deepesh Mar 19 '15 at 11:34
  • The native mysql data types are `date`,`datetime`,`timestamp` and the date format would be `Y-m-d` so you may create a new col in the table with one of them, then run an update query as `update table_name set new_col_created = str_to_date(date_col,'%m/%d/%Y')`, then drop the existing date column and rename the new column as old name. Then in the query you do not need `str_to_date` you can directly use `month(date_col)`, also make sure when you insert you insert in `Y-m-d` format into DB – Abhik Chakraborty Mar 19 '15 at 11:38
1

You can find your ans in below link

Select current months records mysql from timestamp column

Hope this will help you.

Community
  • 1
  • 1
lalitpatadiya
  • 720
  • 7
  • 21