Donjwan Donjwan - 2 months ago 8
MySQL Question

MYSQL > Using yearweek() how to select past week

How can I select all records from the last week ( starting from Monday last week) ?

I am trying to use

yearweek()
in my sql query but i was only able to select this week records

Answer

Here is a way to do it. First we need to get the last week Monday from current date, so we can use the following technique

Below will always give the Monday of the current week

mysql> select date_sub(curdate(), interval weekday(curdate()) day) as d ;
+------------+
| d          |
+------------+
| 2016-09-19 |
+------------+

Now to get the Monday of the last week we can adjust the above as

mysql> select date_sub(date_sub(curdate(), interval weekday(curdate()) day), interval 7 day)  as d ;
+------------+
| d          |
+------------+
| 2016-09-12 |
+------------+

And finally to retrieve the data we can do as

select * from your_table
where 
date_column >= date_sub(date_sub(curdate(), interval weekday(curdate()) day), interval 7 day)

http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_weekday

Best is to handle the date calculation i.e. last week Monday on application level and then pass the value to the query.

Comments