TRUNC - Truncate Datetime - Oracle to MySQL Migration

In Oracle, TRUNC function, when applied for a datetime value, truncates it to the specified part (to day, by default). In MySQL, you can use DATE or DATE_FORMAT functions.

Oracle:

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

MySQL:

  -- Truncate the current date and time (convert to DATE value)
  SELECT DATE(SYSDATE());
  # 2016-04-07

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

Oracle:

  -- Truncate the current date to month (the first day of the month is set)
  SELECT TRUNC(SYSDATE, 'MM') FROM dual;
  # 2016-04-01

In MySQL, you can use DATE_FORMAT function as follows:

MySQL:

  -- Truncate the current date and time (set the first day of the month)
  SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-01');
  # 2016-04-01

Typical conversion examples:

• Free Online SQL Conversion Tool
• Oracle to MySQL Migration Reference

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