Jump to content
Larry Ullman's Book Forums
Sign in to follow this  
Ashley

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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?

 

 

 

 

 

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...