Finding "Next Monday" ...

To illustrate the basics, I'm going to show how you can use some simple mysql functions to determine a date in the future -- in this case, the date for "Next Monday". I'm going to use some static dates to illustrate the basic technique, and these also span a month boundary. The examples provided illustrate an advantage of this technique, which is that it uses MySQL's underlying date arithmetic, which understands the way the Gregorian Calender functions. It's a completely generic facility that will work properly for any day of the year, and will always accurately return the day that is equivalent to "Monday of the following Week".

So let's start with a date in the past -- November 30, 2009.



If you pop open a Calendar, you'll see that November 30th of 2009 was indeed a Monday. As I showed in my previous article, you can use DATE_ADD() to take one date and find another one relative to it, using any of the many different MySQL INTERVALS. In this case we're only going to use 'DAYS'. Clearly if we're on a Monday, it would be logical to think that if we were to add 7 more days, we'd have the date for "Next Monday".



This works perfectly, and correctly provides us the date for the "Following Monday". The problem with this of course, is that it is not generic. Adding 'INTERVAL 7 DAY' will always return us the corresponding day next week. So for example, if we try this for the Tuesday of that week, which is 'Tuesday, December 1, 2009' we will not get the Monday but rather the Tuesday following.



So the next logical question is -- can we find a way to calculate the number of days required for the INTERVAL value, rather than having to provide it as a constant. Here is where one of the many MySQL date functions helps us out. DAYOFWEEK() is a function that returns a number relative to the day of the week, beginning with '1' for Sunday . For our original Monday date in November we can see that it will return a '2'.



If we adjust our calculation to change the static INTERVAL so that it returns 9 minus the DAYOFWEEK() value, we no longer have to hard code INTERVAL 7.



We correctly get the date for the following Monday: December 7, 2009. The beauty of this is that, it will now work for any other day of the week Monday through Saturday. Going back to our previous example, this now also provides us the following Monday, when we present the date for Tuesday, December 1st.



Just to prove the point, here's the date for Saturday December 5th, 2009, which is, as I write this article, a date in the future. We correctly will get the "Following Monday" of December 7th, 2009.



The only problem with this approach, is that it does not work for Sundays. Why?



Because for Sunday, the DAYOFWEEK() is = 1. Our method falls apart, because what we really want for Sunday, is to simply add 1 Day so we roll over to the following Monday. In this case, MySQL's approach doesn't entirely match our way of looking at Weeks. It would be nice if there was a way to get the MySQL DAYOFWEEK() function to number days the way we want, but the next best thing, is simply to find a way to provide a value that will work on Sunday. So that we can continue to use our 9 - DAYOFWEEK() idea, we just need to have MySQL provide an '8' on Sunday, in order for things to continue to work, when we subtract from 9.

Fortunately MySQL give a simple little ternary function named IF() that can be used in occasions like this to get around these types of problems.



Just to prove that this continues to work for other days of the week, we can test this against the original Monday we started with, and prove that it still works.



All that remains is to make this truly generic, by substituting CURDATE() for our date constants.



When I wrote this article:




So we're expecting the 7th, and this gives us "Next Monday."



Just to answer in advance the inevitable question in regards to "Monday of Last Week" we can find this without needing an IF() workaround.