TO_DATE - Convert String to Datetime - Oracle to MariaDB Migration

In Oracle, the TO_DATE function converts a string value to DATE data type value using the specified format.

In MariaDB, you can use the STR_TO_DATE function. Note that the TO_DATE and STR_TO_DATE format strings are different.

Oracle:

  -- Specify a datetime string literal and its exact format
  SELECT TO_DATE('2020-10-25', 'YYYY-MM-DD') FROM dual;

MariaDB - Oracle Compatibility:

  -- Specify a datetime string literal and its exact format
  SELECT STR_TO_DATE('2020-10-25', '%Y-%m-%d');

When you convert Oracle TO_DATE function to STR_TO_DATE function in MariaDB, you have to map the format specifiers:

Oracle TO_DATE		MariaDB STR_TO_DATE
YYYY	4-digit year	%Y
SYYYY	4-digit year with a minus sign for BC dates	%Y
YY	2-digit year	%y
RRRR	2 or 4-digit year, 20th century for 00-49	%Y
RR	2-digit year, 20th century for 00-49	%y
MON	Abbreviated month (Jan - Dec)	%b
MONTH	Month name (January - December)	%M
MM	Month (1 - 12)	%m
DY	Abbreviated day (Sun - Sat)	%a
DD	Day (1 - 31)	%d
HH24	Hour (0 - 23)	%H
HH or HH12	Hour (1 - 12)	%h
MI	Minutes (0 - 59)	%i
SS	Seconds (0 - 59)	%s
SSSSS	Seconds past midnight (0-86399)	Use SEC_TO_TIME() function (see below)

Typical conversion examples:

In Oracle TO_DATE, the SSSSS format specifies the number of seconds past midnight:

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
 
  -- Convert 110 seconds to 1 minute and 50 seconds
  SELECT TO_DATE('110', 'SSSSS') FROM dual;
  /* 2025-12-01 00:01:50 */
 
  SELECT TO_DATE('2025-12-24 110', 'YYYY-MM-DD SSSSS') FROM dual;
  /* 2025-12-24 00:01:50 */

Note that if the date part is not specified it is set to the first day of the current month.

In MariaDB, you can use the SEC_TO_TIME function to convert seconds to TIME:

MariaDB - Oracle Compatibility Mode:

  -- Seconds as string (microseconds as added)
  SELECT SEC_TO_TIME('110');
  /* 00:01:50.000000 */
 
  -- Seconds as integer
  SELECT SEC_TO_TIME(110);
  /* 00:01:50 */
 
  -- Get the same result as Oracle TO_DATE including the date part (the first day of the current month)
  SELECT CAST(CONCAT(SUBSTR(NOW(), 1, 8), '01 ', SEC_TO_TIME(110)) AS DATETIME);
  /* 2025-12-01 00:01:50 */

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