CODEWITHSUNDEEP
mysqlsql
Basudev
Basudev

Reputation: 155

Get data for current month from database

I have a table setup as shown below.

Table Name: activity.

activity_id , Date       , assign_engr , Task_Type , Task_Status
1           , 2013-12-31 , Sachin      , Monthly   , Scheduled
2           , 2013-12-23 , Mikel       , Weekly    , Done
3           , 2013-10-18 , John        , Monthly   , Done

I want to get data for the current month only using Date field as shown below:

select * from activity where Date='current month'

Can anyone help me with this query?

Upvotes: 3

Views: 5044

Answers (3)

peterm
peterm

Reputation: 92785

Here is index-friendly alternative (assuming that date doesn't contain time components, meaning it's of type DATE)

SELECT *
  FROM activity
 WHERE date BETWEEN LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH
                AND LAST_DAY(CURDATE())

Note:

  1. Make sure that you have an index on date column
  2. Don't apply any functions (e.g. MONTH(), YEAR()...) to the column you're searching on because it invalidates usage of any index(indices) you might have on it effectively causing a full scan on the table.

Here is SQLFiddle demo

Upvotes: -1

Sg'te'gmuj
Sg'te'gmuj

Reputation: 151

It appears you're storing dates in MySQL date format, so this should work:

SELECT * FROM activity
WHERE date_format(activity.`Date`, '%Y-%m') = date_format(now(), '%Y-%m')

You need to restrict to year as well, I'm assuming at least; otherwise just checking the month value will get that month for all years.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

You may try like this:

select * from activity where MONTH(`Date`)=MONTH(NOW()) 
and YEAR(`Date`)=YEAR(NOW())

Upvotes: 6

Related Questions