Ashley Posted February 13, 2012 Share Posted February 13, 2012 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 More sharing options...
Larry Posted February 13, 2012 Share Posted February 13, 2012 Have you checked out the MySQL manual's pages for the date and time related functions? You should find what you need there. If not, let us know. Link to comment Share on other sites More sharing options...
Ashley Posted February 14, 2012 Author Share Posted February 14, 2012 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 More sharing options...
Larry Posted February 15, 2012 Share Posted February 15, 2012 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. 2 Link to comment Share on other sites More sharing options...
Ashley Posted February 15, 2012 Author Share Posted February 15, 2012 Thank you so much Larry, using the example you have given I should be able to recreate all the queries that I have listed above, I did not give January a thought that being the first month of the year would cause problems Link to comment Share on other sites More sharing options...
Larry Posted February 15, 2012 Share Posted February 15, 2012 You're welcome. Please let us know how it goes and if you have any other questions. Link to comment Share on other sites More sharing options...
Recommended Posts