TRUNC - Truncate Datetime - Oracle to MySQL Migration

In Oracle, the TRUNC(datetime, 'unit') function truncates a datetime value to the specified unit (for example, setting the time to zero or setting the date to the first day of the month). By default, it truncates to the day.

In MySQL, you can use the CAST, DATE and DATE_FORMAT functions to get the same result.

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- 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

MySQL:

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

Oracle TRUNC for datetime to MySQL conversion:

	Oracle	MySQL
Syntax	TRUNC(datetime [, unit])	CAST, DATE and DATE_FORMAT expressions depending on unit
Default Unit	'DD' truncates to day (sets zero time)

TRUNC function supports the following truncation units:

Oracle TRUNC Unit			Truncation	Result
'DD'	'DDD'		Day	YYYY-MM-DD 00:00:00
'MM'	'MONTH'	'MON'	Month	YYYY-MM-01 00:00:00
'YY'	'YYYY'	'YEAR'	Year	YYYY-01-01 00:00:00
'HH'	'HH24'		Hour	YYYY-MM-DD HH:00:00
'MI'			Minute	YYYY-MM-DD HH:MI:00
'IW'			First day of ISO week (Monday)	YYYY-MM-DD 00:00:00

Conversion summary:

Oracle		MySQL
TRUNC(exp [,'DD'])	Truncate to day	CAST(DATE(exp) AS DATETIME)
TRUNC(exp, 'MM')	Truncate to month	CAST(DATE_FORMAT(exp, '%Y-%m-01') AS DATETIME)
TRUNC(exp, 'YY')	Truncate to year	CAST(DATE_FORMAT(exp, '%Y-01-01') AS DATETIME)
TRUNC(exp, 'MI')	Truncate to minute	CAST(DATE_FORMAT(SYSDATE(), '%Y-%m-%d %H:%i:00') AS DATETIME)

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

For more information, see Oracle to MySQL Migration.