CODEWITHSUNDEEP
mysql
cruim
cruim

Reputation: 319

Select data from last monday to sunday

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

Upvotes: 1

Views: 4979

Answers (4)

Louie Miranda
Louie Miranda

Reputation: 1159

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
;

enter image description here

Upvotes: 0

Galley
Galley

Reputation: 633

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

Upvotes: 0

SUKUMAR S
SUKUMAR S

Reputation: 335

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)

Upvotes: 0

Viki888
Viki888

Reputation: 2774

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.

Upvotes: 1

Related Questions