TRUNC - Truncate Datetime - Oracle to MariaDB Migration

In Oracle, TRUNC function, when applied for a datetime value, truncates it to the specified part (to day, by default). In MariaDB, you have to use CAST, DATE and DATE_FORMAT functions even in Oracle Compatibility mode.

Oracle:

  -- Truncate the current date and time (time part will be set to 00:00:00)
  SELECT TRUNC(SYSDATE) FROM dual;
  # 2020-10-25 00:00:00
 
  SELECT TRUNC(SYSDATE, 'DD') FROM dual;
  # 2020-10-25 00:00:00

MariaDB - Oracle Compatibility:

  -- Truncate the current date and time (convert to DATE value)
  SELECT CAST(DATE(SYSDATE()) AS DATETIME);
  # 2020-10-25 00:00:00

In Oracle, you can truncate datetime to minute by specifying 'MI' in the second parameter:

Oracle:

  -- Truncate the current date to minute (seconds are set to 00)
  SELECT TRUNC(SYSDATE, 'MI') FROM dual;
  # 2020-10-25 23:51:00

In MariaDB, you can use DATE_FORMAT function as follows:

MariaDB:

  -- Truncate the current date to minute (seconds are set to 00)
  SELECT CAST(DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%i:00') AS DATETIME);
  # 2020-10-25 23:51:00

Typical conversion examples:

For more information, see Oracle to MariaDB Migration - Oracle Compatibility Mode.