TRUNC - Truncate Datetime - Oracle to MariaDB 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 MariaDB, the TRUNC(datetime, 'unit') function is available since version 12.2 in the Oracle Compatibility mode only. In earlier versions, you can use the CAST, DATE and DATE_FORMAT functions.

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

MariaDB:

  -- 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 MariaDB conversion:

	Oracle	MariaDB
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		MariaDB
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)

For more information, see Oracle to MariaDB Migration.