MySQL - Implementing Oracle NEXT_DAY - Getting Next DATE by Day Name

What is the date of the next Sunday? In Oracle, you can use NEXT_DAY function to answer this question. In MySQL, you can use an user-defined function.

In Oracle, NEXT_DAY function returns the date of the first day specified by its name later than the specified start day:

Oracle:

  -- Get the date of the next Sunday (current date is Thursday, November 22, 2012)
  SELECT NEXT_DAY(SYSDATE, 'Sun') FROM dual;  
  # 25-NOV-2012

You can also use NEXT_DAY to get the previous date by subtracting 7:

  -- Get the date of the previous Sunday (current date is Thursday, November 22, 2012)
  SELECT NEXT_DAY(SYSDATE, 'Sun') - 7 FROM dual;  
  # 18-NOV-2012

You can use the following user-defined function to implement NEXT_DAY in MySQL:

MySQL:

  DELIMITER //
 
  CREATE FUNCTION next_day(start_date DATETIME, weekday CHAR(20))
    RETURNS DATETIME
  BEGIN
    DECLARE start DATETIME;
    DECLARE i INT;
 
    // Select the next date
    SET start = ADDDATE(start_date, 1);
    SET i = 1;
 
    days: LOOP
          -- Compare the day names
        IF SUBSTR(DAYNAME(start), 1, 3) = SUBSTR(weekday, 1, 3) THEN	
	   LEAVE days;
        END IF;
 
        // Select the next date
        SET start = ADDDATE(start, 1);
        SET i = i + 1;
 
        -- Not valid weekday specified
        IF i > 7 THEN
	   SET start = NULL;
	   LEAVE days;
 	END IF;
 
     END LOOP days;
 
     RETURN start;
  END;
  //
 
   DELIMITER ;

After you created user-defined function NEXT_DAY, you can query the next and previous days as follows:

MySQL:

  -- Get the date of the next Sunday (current date is Thursday, November 22, 2012)
  SELECT NEXT_DAY(NOW(), 'Sun');  
  # 2012-11-25 17:20:46

You can also use NEXT_DAY and SUBDATE functions to get the previous date:

  -- Get the date of the previous Sunday (current date is Thursday, November 22, 2012)
  SELECT SUBDATE(NEXT_DAY(NOW(), 'Sun'), 7);  
  # 2012-11-18 17:22:50

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - November 2012.