How to get dates that are between the last Monday and the current day?

Question

I have dates in my database.

My database is in MySQL.

I want to get dates from my database, which provides me with dates from the last Monday to the current day.

How can i do this?

Answer 1

First you need to find out how many days ago the last Monday passed using the DAYOFWEEK function, and then subtract from the current date -

 SELECT * from table WHERE date >= DATE_SUB(CURDATE(),INTERVAL MOD(DAYOFWEEK(CURDATE())-2,7) DAY) AND date <= DATE_ADD(CURDATE(), INTERVAL MOD(7 - (DAYOFWEEK(CURDATE()) - 1), 7) DAY) 

I'm not 100% sure about the numbers +/- here, you should be able to fix it, though

EDIT: if it will ever be executed only on Sunday at the end of the period, there is a much simpler version -

 SELECT * from table WHERE date >= DATE_SUB(CURDATE(), INTERVAL 6 DAY) AND date <= CURDATE() 

Answer 2

try this one

 select * from table WHERE date >date_sub(curdate(), interval WEEKDAY(curdate()) day) ; 

Answer 3

You can always use the function between your requests ...

 SELECT * FROM orders WHERE order_date between to_date ('2003/01/01', 'yyyy/mm/dd') AND to_date ('2003/12/31', 'yyyy/mm/dd'); 

http://www.techonthenet.com/sql/between.php