CODEWITHSUNDEEP
mysql
Harshali
Harshali

Reputation: 281

mysql: How to get next date of the selected day?

My problem is, I have a form named 'weekly-off setting' in which I am selecting a one or two days as a weekly off.If I have weekly off on saturday then I want to find out the first saturdays date which is coming in the first week of the selected month.So can anyone tell me the mysql query for this problem.

Thanks in advance.

Upvotes: 4

Views: 3038

Answers (1)

Alain Collins
Alain Collins

Reputation: 16362

It's a little unclear from your statement, but you're trying to find the next Saturday?

select date_add(now(), interval 7-dayofweek(now()) day);

Which unfortunately will return today if you're on a Saturday, so the sequence becomes:

SET @OFFSET = 7-dayofweek(now());
SET @OFFSET = IF(@OFFSET = 0, 7, @OFFSET);
select date_add(now(), interval @OFFSET day);

which can be combined into one:

select date_add(now(), interval IF(7-dayofweek(now()) = 0, 7, 7-dayofweek(now())) day) as next;

Upvotes: 4

Related Questions