CODEWITHSUNDEEP
mysql
Brandin Arsenault
Brandin Arsenault

Reputation: 152

MySQL Select From Prior Week, not just 7 days

I am attempting to get a query that selects the week prior (Sun-Sat). I've fought with this query and the closest I can get is the last 7 days, using the following:

SELECT * 
 FROM dates 
 WHERE date BETWEEN CURDATE()-INTERVAL 1 WEEK AND CURDATE();

I'm really unsure how to proceed from here. It seems as if I need to create some kind of relation between CURDATE() and the Saturday before maybe?

Any help is appreciated.

Upvotes: 0

Views: 45

Answers (1)

brian chandley
brian chandley

Reputation: 1276

You are after the Week of the year.

Look at the Week Function: WEEK(date[,mode]) http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_week

The mode describes how you define the week (which is the start of the week) WEEK(date,3) is for a week that starts Monday.

SELECT * 
 FROM dates 
 WHERE 
-- Last Week
WEEK(date,3)  = WEEK(CURDATE(),3)-1 

AND YEAR(date)= YEAR(CURDATE())  ;

Don't forget the year. Week is just a number between 1 and 52. So the Year is important! The code above is not correct. it will fail on the last week of the year!

Upvotes: 1

Related Questions