Skip to content

Finding "Next Monday" using MySQL Dates

Several people who read my article on Exploring Mysql CURDATE and NOW. The same but different posed questions regarding how to return a valid MySQL date equivalent to "Next Monday" given any particular day, as determined by MySQL's CURDATE(). This is a little bit tricky, but can be done entirely in MySQL syntax, making it usable with Calendar applications built on top of MySQL queries, without the need to use serverside date functions. This builds upon concepts discussed in my prior article, so if you have trouble understanding the implications of using CURDATE or DATE_ADD, you should probably take a minute and read that article.
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.


mysql> SELECT DATE_FORMAT('2009-11-30', '%a') as DayOfWeek;
+-----------+
| DayOfWeek |
+-----------+
| Mon       |
+-----------+
1 row in set (0.00 sec)
 


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".


mysql> SELECT DATE_ADD('2009-11-30', INTERVAL 7 DAY) as NEXTMONDAY;
+------------+
| NEXTMONDAY |
+------------+
| 2009-12-07 |
+------------+
1 row in set (0.00 sec)
 


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.


mysql> SELECT DATE_ADD('2009-12-01', INTERVAL 7 DAY) as NEXTMONDAY;    
+------------+
| NEXTMONDAY |
+------------+
| 2009-12-08 |
+------------+
1 row in set (0.00 sec)
 


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'.


mysql> SELECT DAYOFWEEK('2009-11-30');
+-------------------------+
| DAYOFWEEK('2009-11-30') |
+-------------------------+
|                       2 |
+-------------------------+
1 row in set (0.00 sec)
 


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.


mysql> SELECT DATE_ADD('2009-11-30', INTERVAL (9 - DAYOFWEEK('2009-11-30')) DAY) as NEXTMONDAY;
+------------+
| NEXTMONDAY |
+------------+
| 2009-12-07 |
+------------+
1 row in set (0.00 sec)
 


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.


mysql> SELECT DATE_ADD('2009-12-01', INTERVAL (9 - DAYOFWEEK('2009-12-01')) DAY) as NEXTMONDAY;
+------------+
| NEXTMONDAY |
+------------+
| 2009-12-07 |
+------------+
1 row in set (0.00 sec)
 


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.


mysql> SELECT DATE_ADD('2009-12-05', INTERVAL (9 - DAYOFWEEK('2009-12-05')) DAY) as NEXTMONDAY;
+------------+
| NEXTMONDAY |
+------------+
| 2009-12-07 |
+------------+
1 row in set (0.00 sec)
 


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


mysql> SELECT DATE_ADD('2009-12-06', INTERVAL (9 - DAYOFWEEK('2009-12-06')) DAY) as NEXTMONDAY;
+------------+
| NEXTMONDAY |
+------------+
| 2009-12-14 |
+------------+
1 row in set (0.00 sec)
 


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.


mysql> SELECT DATE_ADD('2009-12-06', INTERVAL (9 - IF(DAYOFWEEK('2009-12-06')=1, 8, DAYOFWEEK('2009-12-06'))) DAY) as NEXTMONDAY;
+------------+
| NEXTMONDAY |
+------------+
| 2009-12-07 |
+------------+
1 row in set (0.00 sec)
 


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.


mysql> SELECT DATE_ADD('2009-11-30', INTERVAL (9 - IF(DAYOFWEEK('2009-11-30')=1, 8, DAYOFWEEK('2009-11-30'))) DAY) as NEXTMONDAY;
+------------+
| NEXTMONDAY |
+------------+
| 2009-12-07 |
+------------+
1 row in set (0.01 sec)
 


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


SELECT DATE_ADD(CURDATE(), INTERVAL (9 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY) AS NEXTMONDAY;
 


When I wrote this article:


mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2009-12-01 |
+------------+
1 row in set (0.00 sec)
 



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


mysql> SELECT DATE_ADD(CURDATE(), INTERVAL (9 - IF(DAYOFWEEK(CURDATE())=1, 8, DAYOFWEEK(CURDATE()))) DAY) as NEXTMONDAY;
+------------+
| NEXTMONDAY |
+------------+
| 2009-12-07 |
+------------+
1 row in set (0.00 sec)
 


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


SELECT DATE_ADD(CURDATE(), INTERVAL (5 + DAYOFWEEK(CURDATE())) * -1 DAY) AS LASTMONDAY;
 



mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2009-12-02 |
+------------+
1 row in set (0.00 sec)

mysql> SELECT DATE_ADD(CURDATE(), INTERVAL (5 + DAYOFWEEK(CURDATE())) * -1 DAY) as LASTMONDAY;
+------------+
| LASTMONDAY |
+------------+
| 2009-11-23 |
+------------+
1 row in set (0.00 sec)
 

Trackbacks

No Trackbacks

Comments

Display comments as Linear | Threaded

Stephen Black on :

*I tried to modify your code so that it would give me every Friday but it doesn't work so then I had to make my own code to give me Fridays

SET @CurDate=CURDATE();
SET @CurDate=DATE_ADD(@CurDate, INTERVAL (IF(DAYOFWEEK(@CurDate)>4, 4, 0)) DAY);
SET @CurDate=DATE_ADD(@CurDate, INTERVAL (6-IF(DAYOFWEEK(@CurDate)=5, 6, DAYOFWEEK(@CurDate))) DAY);
SELECT @CurDate;

Andrew Bermejo on :

*Thanks for this.
from here i formulate this.

SELECT @tmpdate:= '2012-01-06',@dayno:=1,DATE_ADD(@tmpdate, INTERVAL ((7+@dayno) - IF(DAYOFWEEK(@tmpdate)

Andrew Bermejo on :

*then I make a function.

DELIMITER $$

DROP FUNCTION IF EXISTS `nextDayOfWeek`$$

CREATE FUNCTION `nextDayOfWeek`( tmpdate DATE, dayno INT) RETURNS DATE
DETERMINISTIC
BEGIN
DECLARE wikday INT(1);
SET wikday = DAYOFWEEK(tmpdate);
RETURN DATE_ADD(tmpdate, INTERVAL ((7+dayno) - IF( wikday

Jade Wood on :

*If you were looking to find the nearest Friday just gone (could be today if today is Friday), then this is what I came up with:

DATE_ADD( CURDATE() , INTERVAL( ( 6 - IF( DAYOFWEEK( CURDATE() ) in(6,7), DAYOFWEEK(CURDATE()), DAYOFWEEK( CURDATE() ) +7 ) ) )
DAY )

Eric Tamo on :

*Thanks for Jade. your code gave me an idea on how to get the following friday of a given day.

SELECT DATE_ADD(CURDATE() , INTERVAL( ( 6 - IF( DAYOFWEEK( CURDATE() ) in(6,7), DAYOFWEEK(CURDATE()),DAYOFWEEK(CURDATE())) ) )
DAY as FRIDAY_OF_DATE

san on :

*Thanks for tutorial.

Katai on :

*Actually, you can change the result of 'dayofweek()' to some degree, with modulo.

You need that a lot if you want a format like '0=monday, etc' instead of sunday.

To make monday == 0, just add '-1, +6, %7'

Example: SELECT ((DAYOFWEEK('2012-07-16') - 1) + 6)% 7

this returns last monday, as 0

SELECT ((DAYOFWEEK('2012-07-22') - 1) + 6) % 7

this returns next sunday as 6

Klaus on :

*Nice stuff. Inspired me to do this:

SELECT
DATE(CURDATE()) AS now_date,
DATE_ADD(DATE(CURDATE()), INTERVAL (8 - DAYOFWEEK(CURDATE())) DAY) AS next_sunday,
DATE_ADD(DATE(CURDATE()), INTERVAL (9 - IF(DAYOFWEEK(CURDATE()) < 2, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_monday,
DATE_ADD(DATE(CURDATE()), INTERVAL (10 - IF(DAYOFWEEK(CURDATE()) < 3, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_tuesday,
DATE_ADD(DATE(CURDATE()), INTERVAL (11 - IF(DAYOFWEEK(CURDATE()) < 4, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_wednesday,
DATE_ADD(DATE(CURDATE()), INTERVAL (12 - IF(DAYOFWEEK(CURDATE()) < 5, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_thursday,
DATE_ADD(DATE(CURDATE()), INTERVAL (13 - IF(DAYOFWEEK(CURDATE()) < 6, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_friday,
DATE_ADD(DATE(CURDATE()), INTERVAL (14 - IF(DAYOFWEEK(CURDATE()) < 7, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_saturday;

\Klaus

kev999 on :

*In this example - you just need to substitute variable @next_dayofweek with the day in the week you want:

SET @date = CURDATE();
SET @next_dayofweek = 3; -- next tuesday(3) (Sunday(1) Saturday(7))
SET @diff = @next_dayofweek - DAYOFWEEK(@date);
SELECT DATE_ADD(@date, INTERVAL (IF(@diff

SimonB on :

*I just wanted to offer this solution as I was very confused by this whole thing.

This solutions assumes you store a start date, end date and a day that the event recurs (1 to 7 sunday first).

@startFrom := IF(CURDATE() < start_date, start_date, CURDATE()),
@dow := DAYOFWEEK(@startFrom),
@interval := IF(@dow > recurring_day, 7 - (@dow - recurring_day), ABS(@dow - recurring_day)),
@occurrence := DATE_ADD(@startFrom, INTERVAL @interval DAY),
IF(@occurrence recurring_day, ABS(@dow - recurring_day), 7 - (@dow - recurring_day))

I hope this helps anyone that needs it and thanks for the tutorial, I learned a lot from it.

Add Comment

Pavatar, Gravatar, Favatar, MyBlogLog, Pavatar author images supported.
BBCode format allowed
E-Mail addresses will not be displayed and will only be used for E-Mail notifications.
Form options