NEXT_DAY Function - Oracle to MariaDB Migration

In Oracle, the NEXT_DAY function returns the date of the next specified weekday following a given date.

In MariaDB, you can use an expression with the DATE_ADD and WEEKDAY functions to get the same result.

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Get the date of next Thursday following 2025-12-15 (Monday), the time part remained unchanged
  SELECT NEXT_DAY(TIMESTAMP '2025-12-15 10:34:17', 'Thursday') FROM dual;
  /* 2025-12-18 10:34:17 */

MariaDB:

  -- Get the date of next Thursday following 2025-12-15 (Monday), the time part remained unchanged
  SELECT DATE_ADD(TIMESTAMP '2025-12-15 10:34:17', 
    INTERVAL (3 /* 'Thursday' */ - WEEKDAY(TIMESTAMP '2025-12-15 10:34:17') + 7) % 7 DAY);
  /* 2025-12-18 10:34:17 */

In MariaDB, you still may need to convert arithmetic expressions involving the function, for example:

Oracle:

  -- Get the next Sunday and add 1 hour
  SELECT NEXT_DAY(TIMESTAMP '2025-12-15 10:34:17', 'Sunday') + 1/24 FROM dual;
  /* 2025-12-21 11:34:17 */

You cannot use DATE_ADD() + 1/24 for the same purpose in MariaDB; you have to use an additional DATE_ADD function:

MariaDB:

  -- Get the next Sunday and add 1 hour
  SELECT DATE_ADD(DATE_ADD(TIMESTAMP '2025-12-15 10:34:17', 
    INTERVAL (6 /* 'Sunday' */ - WEEKDAY(TIMESTAMP '2025-12-15 10:34:17') + 7) % 7 DAY),  
    INTERVAL 1 HOUR);
  /* 2025-12-21 11:34:17 */

For more information, see Oracle to MariaDB Migration.