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 - Oracle Compatibility:

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

TRUNC Conversion Overview

Oracle TRUNC for datetime to MariaDB conversion:

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

Truncate Datetime to Minute (Seconds are set to 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 - Oracle Compatibility:

  -- 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

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

SQLines Data

SQLines SQL Converter

Migration to MariaDB

Migration to MySQL

Migration to PostgreSQL

Migration to SQL Server

Migration to Oracle

SQL in Applications

TRUNC - Truncate Datetime - Oracle to MariaDB Migration

TRUNC Conversion Overview

Truncate Datetime to Minute (Seconds are set to 00)