CODEWITHSUNDEEP
phpmysqldatejoomla
Pritesh Mahajan
Pritesh Mahajan

Reputation: 5154

How to get next week monday to sunday in my sql

I am using below code for fetching next monday but i am unable to fetch next sunday on same week. like today is friday and if i search next week then i need to get next week monday to sunday.

SELECT now() + INTERVAL 7 - weekday(now()) DAY

This above give me next monday.

I need to add 7 more days and get sunday date in same format.

2018-07-30 14:45:43 Monday

2018-08-05 14:45:43 Sunday

Upvotes: 3

Views: 2514

Answers (4)

MandyShaw
MandyShaw

Reputation: 1156

How about

select monday, monday + interval 6 day as sunday from
(select now() + INTERVAL 7 - weekday(now()) DAY as monday) s1

Upvotes: 0

Kenneth
Kenneth

Reputation: 415

If I understand correctly, you have gotten "2018-07-30 14:45:43" and need to get Sunday. Sunday is six days after Monday, so just add 6 to 7 and get 13.

SELECT now() + INTERVAL 13 - weekday(now()) DAY

Upvotes: 1

Alex Howansky
Alex Howansky

Reputation: 53563

The strtotime() function is great for vague relative queries like this:

$monday = date('Y-m-d', strtotime('next monday'));
$sunday = date('Y-m-d', strtotime('next monday + 6 days'));

Yields:

2018-07-30
2018-08-05

Upvotes: 4

Lajos Arpad
Lajos Arpad

Reputation: 76551

Add 6 days to get the sunday of the monday. Add 13 days to add the second sunday after the monday:

SELECT (now() + INTERVAL 7 - weekday(now()) DAY) + INTERVAL 6 DAY;

Upvotes: 3

Related Questions