choloboy choloboy -4 years ago 131
MySQL Question

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:

  • FirstDay --> Feb 20

  • LastDay --> Feb 26

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?

Answer Source

Selecting first and last day of previous week:

 (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:

WHERE dateCol >= (curdate() - INTERVAL((WEEKDAY(curdate()))+7) DAY)
  AND dateCol < (curdate() - INTERVAL((WEEKDAY(curdate()))+1) DAY)
ORDER BY dateCol DESC -- or ASC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download