CODEWITHSUNDEEP
mysql
joe
joe

Reputation: 237

MYSQL fetch records from table only for a particular month

I want to fetch records from mysql table in monthly wise. I donot want to use createDate BETWEEN "2015-04-01" and "2015-04-30" nor createDate > "2015-04-01" and createDate < "2015-04-30".

I have tried : 

YEAR(createDate) = 2015 and MONTH(createDate) = 04  #which is not working.

can anyone tell me how write a query for any particular month.?

Upvotes: 0

Views: 73

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

It should be

MONTH(createDate) = 4

however the approach 

`createDate >= "2015-04-01" 
and createDate <= "2015-04-30"`

is much better since it would utilize the index, where in using the YEAR or MONTH function will not use the index. For a small data-set its ok however for large data set it will overkill and the query will be very slow.

You can make it dynamic something as

where 
createDate >=  date_format(curdate(),'%Y-%m-01')
and
createDate <=  last_day(curdate())

For a particular month you can construct the query as

where 
createDate >=  date_format(curdate(),'%Y-04-01')
and
createDate <=  last_day(date_format(curdate(),'%Y-04-01'))

Upvotes: 1

Related Questions