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

Truncate Datetime to Month (Set the First Day of the Month)

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

Conversion Examples

Typical conversion examples:

Oracle MySQL
1 TRUNC(SYSDATE) DATE_DATE(SYSDATE())
2 TRUNC(SYSDATE, 'DD') DATE(SYSDATE())
3 TRUNC(SYSDATE, 'MM') DATE_FORMAT(SYSDATE(), '%Y-%m-01')

Database and SQL Migration Tools

About SQLines

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

You could leave a comment if you were logged in.