CODEWITHSUNDEEP
phpmysql
sachin Bhalerao
sachin Bhalerao

Reputation: 37

Fetch records from specific month and year in php and mysql

I have table where records are stored in 9/23/2014 this format of date. I have searched a lot but all the result which i got there were in timestamp format(2013-12-27).

Can anybody help me, how retrieve data from specific month (say 'september' 2014)

Many thanks if help.

   Table: events

  id      course      description    date         start      end   
  1       john         test         9/23/2014     1:00pm    3:00pm
  2       brad         play         12/30/2014    5:00pm    8:00pm

 My Code:

        SELECT date FROM events WHERE MONTHNAME(date) = 'September' AND YEAR(date) = '2014'

Upvotes: 1

Views: 3224

Answers (3)

Jay Blanchard
Jay Blanchard

Reputation: 34416

You should use BETWEEN - 

SELECT * 
FROM `events` 
WHERE `date` BETWEEN '2013-09-01' AND '2013-09-30';

Because your dates are stored as varchar's (you really should fix that) you'll have to convert the string to a date format and then search. The performance will be awful though - 

WHERE STR_TO_DATE(`date`, '%m/%/d/%Y %h:%i:%s %p') BETWEEN STR_TO_DATE('2013-09-01', '%m/%/d/%Y') AND STR_TO_DATE('2013-09-30', '%m/%/d/%Y')

or

WHERE STR_TO_DATE(LEFT(`date`,LOCATE(' ',`date)),'%m/%d/%Y') BETWEEN '2013-09-01' AND '2013-09-30';

You can also use this method (although I prefer the first because of the control it offers) - 

SELECT * FROM `table` WHERE YEAR(`date_column`) = 2013 AND MONTH(`date_column`) = 9;

EDIT: Updated to reflect the OP's table schema. You should always check reserved word information before naming your columns.

Upvotes: 3

Ricardo Ribeiro
Ricardo Ribeiro

Reputation: 1

You can try something like this:

SELECT * FROM my_table WHERE date_field BETWEEN DATE_FORMAT('%m/%d/%Y','2013-09-01') AND DATE_FORMAT('%m/%d/%Y','2013-09-30')

It should work.

Upvotes: 0

Kelvin Kehinde Omolumo
Kelvin Kehinde Omolumo

Reputation: 197

Simple try this:

$query = mysqli_query($connect,'SELECT columns FROM tablename WHERE MONTHNAME(datecolumnn) = 'September' AND YEAR(datecolumn) = '2014');

;)

Upvotes: 0

Related Questions