Take the 2-minute tour ×

is there a simpler way than writing:

select date_sub(curdate(), interval WEEKDAY(curdate()) day) as LastMonday
from dual

share|improve this question
I think that that is as simple as you will get it. –  Jambobond Aug 13 '09 at 9:34
add comment

3 Answers

up vote 13 down vote accepted

If you're not using an ancient MySQL, you can wrap this in a stored function.


and then call

select LastMonday() as LastMonday


If you're having performance problems, you can persist the value in a session variable. That way you can be sure that it will only be calculated once.

set @LastMonday=LastMonday();
select @Lastmonday;

(in this simple query it makes no difference of course...)

share|improve this answer
that's exactly how I had "avoided" the long looking date calculations up to now ... I was/am worried about potential overhead ... –  lexu Aug 13 '09 at 14:32
I've since added your "performance update" suggestion to our codebase. It makes a huge difference, somehow I'd expected the function be called only once .. (but how is the optimizer to know I'm not returning time in milliseconds ..) –  lexu Oct 16 '09 at 6:25
The trick here is that the value is calculated once and then cached manually. You could try what the optimizer does. It might be smart enough to cache it too, but you can't really rely on it. –  Wouter van Nifterick Oct 16 '09 at 11:44
add comment
SET @dateCurrent = CURDATE();
SET @dateCurrentDayInWeek = DAYOFWEEK(@dateCurrent) - 2;
SET @dateLastMonday = DATE_ADD(@dateCurrent, INTERVAL -@dateCurrentDayInWeek DAY);

SELECT @dateLastMonday;
share|improve this answer
add comment

Try this:

-- Today is 05 April 2013

-- Get Last Monday from MySQL

SELECT DATE_FORMAT(LAST_DAY(NOW()) - ((7 + WEEKDAY(LAST_DAY(NOW())) - 7) % 7), '%Y-%m-%d') last_monday;

-- Output

share|improve this answer
For me that's neither simpler, nor more performant than the code in the question? .. but I'll take the hint and will re-phrase the question title! –  lexu Apr 8 '13 at 6:52
For me, this outputs not next Monday but the Monday after that. –  Dan Maguire Jun 13 '13 at 21:37
add comment

Your Answer


