Finding "Next Monday" using MySQL Dates Wed, Dec 2. 2009
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)
- PHP Mysql support: mysql or mysqlnd?
- Calculate a person's age in a MySQL query -- continued
- Calculate a person's age in a MySQL query
- SQL UPDATE for strings in MongoDB
- Centos Virtual LAMP server -- Part II
- Too much information about the MySQL TIMESTAMP
- Mysql Update: Null + 1 is Null!
- LAMP Tutorial Series originally published on PHPFreaks.com
#1 - Stephen Black 2011-03-19 07:05 - (Reply)
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;
#2 - Andrew Bermejo said:
2012-01-03 05:37 - (Reply)
Thanks for this.
from here i formulate this.
SELECT @tmpdate:= '2012-01-06',@dayno:=1,DATE_ADD(@tmpdate, INTERVAL ((7+@dayno) - IF(DAYOFWEEK(@tmpdate)
#3 - Andrew Bermejo 2012-01-03 05:39 - (Reply)
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
#4 - Jade Wood said:
2012-02-02 00:21 - (Reply)
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 )
#4.1 - Eric Tamo 2013-05-28 04:53 - (Reply)
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
#6 - Katai 2012-07-19 09:09 - (Reply)
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
#7 - Klaus 2015-07-17 07:31 - (Reply)
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
#8 - kev999 2016-05-19 19:22 - (Reply)
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
#9 - SimonB 2020-04-29 14:09 - (Reply)
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.