Search code examples
mysqldateweekday

How to get last Sunday and Sunday before last using MySQL?

I am trying to get the date of last Sunday and the date of the Sunday before that.

For example: 

Today (Tuesday) is '20190604'  
@Last_Sunday = '20190602'  
@Sunday_B4_Last = '20190526'

I know how to transfer the date to ISO format but i do not know how to get it to show Sundays.

I found answers to the same question but in other languages like PostgreSQL and JS but nothing for MySQL.

I found some code online that does something very similar but it shows Friday and I was not able to make it show the Friday before that.

Here is the code:

select now() - interval (weekday( now())+ 2)% 7+ 1 day 'Previous Friday';

I would appreciate any answers but especially ones that explain HOW to manipulate the formula above or any other formula that gives the desired result.

Solution

  • An option:

    SELECT
  `dates`.`date` - INTERVAL WEEKDAY(`dates`.`date`) % 7 + 1 DAY `Last`,
  `dates`.`date` - INTERVAL WEEKDAY(`dates`.`date`) % 7 + 8 DAY `Previous`
FROM (
  SELECT '2019-06-04' `date`
) `dates`;

    See dbfiddle.