How to get dates which are between last monday till current day?

I am having dates in my database.

My data开发者_StackOverflowbase is in MySQL.

I want to fetch dates from my database which provides me dates from last monday till current day.

How can I do that?

You first have to work out how many days ago last monday was, using the DAYOFWEEK function, then subtract that 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 work it out from this though

EDIT: If this will only ever be run on the 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()

try this one

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

You could always use the between function in your queries...

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