mysqlsql

MySQL - First & Last Day of Previous Week

I am trying to get the first and last day of the previous week in MySQL, where the first and last days are Monday and Sunday, respectively.

For example if today (March 1, 2017) is when the SQL is executed I would like to obtain:

I have tried the following and all sorts of variations with this, without success.

SELECT DATE_ADD(curdate(), INTERVAL(-WEEKDAY(curdate())) DAY);

Is it possible to achieve these results when executed anytime within the current week?

Solution

  • Selecting first and last day of previous week:

    SELECT
 (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY) as e,
 (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY) as s

    s: 2017-02-20

    e: 2017-02-26

    And if you want filter last week table rows:

    SELECT * FROM tblName
WHERE dateCol >= (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY)
  AND dateCol < (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY)
ORDER BY dateCol DESC -- or ASC