LAST_DAY Function - Oracle to MySQL Migration

In Oracle and MySQL, LAST_DAY function returns the date of the last day of the month for the specified datetime expression.

Note that Oracle LAST_DAY returns the DATE data type that includes both date and time, while MySQL LAST_DAY returns the DATE data type that includes the date part only (year, month and day).

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Returns last day of May
  -- Note that the time part remained unchanged
  SELECT LAST_DAY(TIMESTAMP '2021-05-25 23:18:13') FROM dual;
  # 2021-05-31 23:18:13

MySQL:

  -- Returns last day of May
  -- Note that the time part was removed
  SELECT LAST_DAY(TIMESTAMP '2021-05-25 23:18:13');
  # 2021-05-31

Although MySQL has the LAST_DAY function you still may need to convert arithmetic expressions involving the function, for example:

Oracle:

  -- Get the first day of the next month
  SELECT LAST_DAY(SYSDATE) + 1 FROM dual;
  # 2021-06-01 17:43:27

You cannot use LAST_DAY() + 1 for the same purpose in MySQL, you have to use DATE_ADD function:

MySQL:

  -- Arithmetics expression + 1 produces an integer result
  SELECT LAST_DAY(SYSDATE()) + 1;
  # 20210532
 
  -- Use DATE_ADD to get the correct result 
  SELECT DATE_ADD(LAST_DAY(SYSDATE()),  INTERVAL 1 DAY);
  # 2021-06-01

For more information, see Oracle to MySQL Migration.