CODEWITHSUNDEEP
mysql
Rohit
Rohit

Reputation: 1550

Get next date based on the day number - mysql

I am new to mysql and I want to write a query where I pass in the day number and it gives me the next date in the coming/current week.

Examples :

  1. If today is Monday (01-Jan-2018) and I pass in 5, then I want Friday (05-Jan-2018).
  2. If today is Friday (05-Jan-2018) and I pass in 1, then I want Monday (08-Jan-2018).

I have written a basic query but I am not able to make it generic.

Query :

select date_add(now(), interval dayofweek(now()) + (6 - @day_num)  day);

Upvotes: 1

Views: 1191

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

Try (it can be optimized further, but first can you check if it works):

SELECT 
  CASE WHEN WEEKDAY(CURDATE()) + 1 >= @day_num
       THEN (CURDATE() + INTERVAL (6 - WEEKDAY(CURDATE())) DAY) + INTERVAL @day_num DAY
       ELSE (CURDATE() + INTERVAL (0 - WEEKDAY(CURDATE())) DAY) + INTERVAL (@day_num-1) DAY
  END AS next_date;

Upvotes: 2

Related Questions