Select data from last monday to sunday

Question

How to select data from last monday to sunday. Like this

`WHERE
   `order`.order_createdAt >= date_sub(date_sub(curdate(), interval day(curdate()) - 1 day), interval 1 month) 
and `order`.order_createdAt < date_sub(curdate(), interval day(curdate()) - 1 day)`

this show data from last month Upd. find this

`WHERE WEEK (order_createdAt) = WEEK( current_date)-1 
AND YEAR( order_createdAt) = YEAR( current_date );`

But it takes from past sunday to saturday

Provide some sample data or create your sql fiddle http://sqlfiddle.com/ and ask for some expected output — Shivkumar kondi, Jan 27 '17 at 06:36
http://stackoverflow.com/questions/12422248/previous-monday-previous-sundays-date-based-on-todays-date — s-sharma, Jan 27 '17 at 06:49
[this](http://stackoverflow.com/questions/17912871/how-to-set-sql-to-find-records-from-last-sunday-to-this-sunday-1-week) might help you . — Punit Gajjar, Jan 27 '17 at 07:07

score 1 · Accepted Answer · answered Jan 27 '17 at 07:34

1

If you want to check for Last week Monday to This Sunday, which is say Today's date is '2017-01-27' and Last week Monday date will be 2017-01-16 and This Sunday will be 2017-01-22, then you can follow below query,

WHERE
   `order`.order_createdAt BETWEEN subdate(curdate(),dayofweek(curdate())+5)
and subdate(curdate(),dayofweek(curdate())-1)`

Hope this would help you out.

answered Jan 27 '17 at 07:34

Viki888

2,686
2
13
16

This is show data from past monday to saturday. Are you sure that this query will get same data tomorow? – cruim Jan 27 '17 at 08:39
It will get same data tomorrow too. – Viki888 Jan 27 '17 at 09:34

score 0 · Answer 2 · answered Jan 03 '20 at 06:20

Following SQL code might be useful to Presto users who might be searching for same information in reference to same question asked, for data between Last Monday to Next Sunday (an ISO week): -

WHERE date_column_ref BETWEEN date_add('day', dow(localtimestamp) * -1 + 1, localtimestamp) and date_add('day', 7 - dow(localtimestamp), localtimestamp)

score 0 · Answer 3 · answered Jan 14 '20 at 03:29

0

select subdate(curdate(), WEEKDAY(curdate()) + 7); # Monday
select subdate(curdate(), WEEKDAY(curdate()) + 1); # Sunday

answered Jan 14 '20 at 03:29

Galley

493
6
9

2

Please put your answer always in context instead of just pasting code. See [here](https://stackoverflow.com/help/how-to-answer) for more details. – gehbiszumeis Jan 14 '20 at 06:42

score 0 · Answer 4 · answered Jun 30 '21 at 06:27

I been trying to search for the same issue on getting MONDAY to SUNDAY, from a specific day.

I have come-up with the following and hope this helps anyone who is looking for the same solution as I am.

The only issue I have is, I think this can be improved and open for suggestions as it's long.

SELECT 
    DATE_ADD(DATE('2021-05-30 02:12:43'),
        INTERVAL - WEEKDAY(DATE('2021-05-30 02:12:43')) DAY) AS MONDAY,
    DATE_ADD(DATE_ADD(DATE('2021-05-30 02:12:43'),
            INTERVAL - WEEKDAY(DATE('2021-05-30 02:12:43')) DAY),
        INTERVAL 6 DAY) AS SUNDAY
;

Select data from last monday to sunday

4 Answers4