How do I retrieve data for the previous month in SQL

Question

I want to get data for the dates between 2015-05-01 and 2015-06-01 using SQL. Please help me with the query.

The query I used is:

select *,count(id) as multiple_visitors 
    from table1 
    where id=123 
        and (date(server_time) between (CURDATE() - INTERVAL 31 DAY) AND CURDATE()) 
    group by user_id having count(id)>1

hard to get it with no information about your database/table structure... — Julo0sS, Jul 08 '15 at 11:34

score 1 · Answer 1 · answered Jul 08 '15 at 11:40

You can do this with month() and year():

where month(server_time) = month(curdate() - interval 1 month) and
      year(server_time) = year(curdate() - interval 1 month)

However, I recommend a slightly more complex expression:

where server_time >= date_sub(date_sub(curdate(), interval - day(curdate()) + 1 day), interval 1 month) and
      server_time < date_sub(curdate(), interval - day(curdate()) + 1 day)

The advantage is that there are no functions on server_time, so the database engine can use an index, if appropriate.

As a note: the expression date_sub(curdate(), interval - day(curdate()) + 1 day) gets midnight on the first day of the month.

Justin W. Johns · Answer 2 · 2015-07-08T12:04:25.397

0

Try using "WHERE" with MONTH(date).

Like this:

  SELECT * FROM Table
  WHERE MONTH(date) = 1

edited Jul 08 '15 at 12:04

answered Jul 08 '15 at 11:27

Justin W. Johns

119
1
8

How do I retrieve data for the previous month in SQL

2 Answers2