3

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?

Strawberry
  • 33,750
  • 13
  • 40
  • 57
Basudev
  • 155
  • 5
  • 13

3 Answers3

6

You may try like this:

select * from activity where MONTH(`Date`)=MONTH(NOW()) 
and YEAR(`Date`)=YEAR(NOW())
Rahul Tripathi
  • 168,305
  • 31
  • 280
  • 331
1

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.

Sg'te'gmuj
  • 151
  • 1
  • 2
  • 5
  • 1
    Thanks; Rahul's answer was good, but just wanted to clarify that alternative to be safe. That way Basudev doesn't have to learn the hard way like the rest of us :P. – Sg'te'gmuj Dec 22 '13 at 06:50
-1

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

peterm
  • 91,357
  • 15
  • 148
  • 157
  • Sorry, no, not always going to work: `2013-02-28` - 1 Month: `2013-01-28` + 1 day: `2013-01-29`; off by 3 days (29th, 30th, 31st). To get the start of the month, subtract the current day - 1. You should also avoid the use of `BETWEEN`, in favor of an exclusive upper-bound (ie less than the start of the next month), which would allow the query to work irrespective of whether `date` has time components. But yes, this basic approach allows indices to be used. – Clockwork-Muse Dec 22 '13 at 13:39
  • @Clockwork-Muse Thanks for the input :). Of course it should be +1day -1month. It was a typo. I was answering too late at night. Now the using of BETWEEN is a matter of preference as long as it's used without the time component. – peterm Dec 22 '13 at 21:45
  • ...And why not make this safe for that situation (with time components), which doesn't take that much more effort? – Clockwork-Muse Dec 23 '13 at 04:41
  • @Clockwork-Muse You've already made you point. I thought I said that before it's a matter of preference when applied appropriately. The whole point of my answer was about completely different and very important issue - getting index support. Now from what I see your answers are not so consistent on that point as one might think (e.g. http://stackoverflow.com/a/7112446/1920232, http://stackoverflow.com/a/10356338/1920232). But **again** thank you. – peterm Dec 23 '13 at 05:24