Jump to content
Larry Ullman's Book Forums

This Month, Last Month, This Year Query


Recommended Posts

Hello Nice People,

 

I am looking to create some mysql queries to display the following

 

I can correctly display today's registered users

Select * FROM users WHERE date(registered) =date( curdate() );

 

and Yesterday's registered users

Select * FROM users WHERE date(registered) =date( curdate() -1);

 

but how do I do the following

 

registered this week

 

registered this month

 

registered last month (not last 30 days)

 

registered this year

 

 

Thank you

Link to comment
Share on other sites

I have read though the date and time functions in the mysql pdf but still must be missing something I have been looking at http://forums.devshed.com/mysql-help-4/select-date-sub-of-today-yesterday-last-month-etc-531528.html where someone appears to be doing a similar project but appears to be going about the wrong way about it.

 

 

For example If I wanted to display the fields relating to last month, this year I assume I would be connecting now(), month(), and Year() functions together. How would I do this ? does anyone know of a weblink showing examples like this and or would anyone be kind enough to write an example of how this could be written?

 

 

 

 

 

Link to comment
Share on other sites

Okay, for the "last month, this year", I assume you mean the previous month. If so, the current month is MONTH(NOW()) so the previous month is MONTH(NOW())-1. You'd want to factor in the year (so you don't get, say, January postings of any year):

SELECT * FROM users WHERE MONTH(registered) = (MONTH(NOW()) - 1) AND YEAR(registered) = YEAR(NOW())

That will work for 11 months of the year, but not January, so you'd have to factor that in using an OR clause:

 

SELECT * FROM users WHERE
(MONTH(registered) = (MONTH(NOW()) - 1) AND YEAR(registered) = YEAR(NOW()))
OR
(MONTH(registered) = 12 AND MONTH(NOW())=1 AND YEAR(registered) = (YEAR(NOW()) - 1)

I *think* that will work. The first conditional is where the month is the previous month and the year is the same year. That would work for February - December. In January, the years wouldn't match, so the second clause wants the registration month to be 12 and the current month to be 1 (January) and the registered year to be last year.

 

Let me know how that works for you.

  • Upvote 2
Link to comment
Share on other sites

 Share

×
×
  • Create New...