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 a 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('2017-12-20', 'YYYY-MM-DD') FROM dual;

MariaDB:

  -- Specify a datetime string literal and its exact format
  SELECT STR_TO_DATE('2017-12-20', '%Y-%m-%d');

You can use SQLines SQL Converter to convert Oracle TO_DATE function to STR_TO_DATE function in MariaDB that maps the format specifiers as follows:

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)

Conversion examples:

If a string literal matches the default DATE format string defined in Oracle, you can use TO_DATE function to convert a string to DATE without specifying the format string:

Oracle:

  -- Let's determine the current default DATE format:
  SELECT value FROM nls_database_parameters WHERE parameter  = 'NLS_DATE_FORMAT';
  # DD-MON-RR
 
  -- So we can convert '21-DEC-17' to DATE without specifying format
  SELECT TO_DATE('21-DEC-17') FROM dual;
  # 21-DEC-17

Note that if NLS_DATE_FORMAT is set to 'YYYY-MM-DD', 'YYYY/MM/DD' or 'YYYY.MM.DD' Oracle TO_DATE recognizes various delimiters:

Oracle:

  -- Set default format to YYYY-MM-DD
  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
 
  -- This format matches NLS_DATE_FORMAT
  SELECT TO_DATE('2021-09-15') FROM dual;
  # 2021-09-15 
 
  -- But other delimiters are also recognized
  SELECT TO_DATE('2021/09/15') FROM dual;
  # 2021-09-15 
 
  SELECT TO_DATE('2021.09.15') FROM dual;
  # 2021-09-15 
 
  -- Including YYYYMMDD format
  SELECT TO_DATE('20210915') FROM dual;
  # 2021-09-15

But when another format is specified Oracle allows only values matching the format:

Oracle:

  -- Set default format to DD-MON-YYYY
  ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
 
  SELECT TO_DATE('2021-09-15') FROM dual;
  # ORA-01861: literal does not match format string
 
  SELECT TO_DATE('2021/09/15') FROM dual;
  # ORA-01861: literal does not match format string
 
  SELECT TO_DATE('2021.09.15') FROM dual;
  # ORA-01861: literal does not match format string
 
  SELECT TO_DATE('20210915') FROM dual;
  # ORA-01861: literal does not match format string

In MariaDB, for some date formats you can also use CAST AS DATETIME function without specifying the date format, for other format you have to use STR_TO_DATE function and specify the format explicitly:

MariaDB:

  -- MariaDB will not cast this string to datetime
  SELECT CAST('21-DEC-17' AS DATETIME);
  # NULL
 
  -- So you have to specify the format
  SELECT STR_TO_DATE('21-DEC-17', '%d-%b-%y');
  # 2017-12-21 
 
  -- But the following formats are recognized: 
  SELECT CAST('2017-12-21' AS DATETIME);
  # 2017-12-21 00:00:00
 
  SELECT CAST('20171221' AS DATETIME);
  # 2017-12-21 00:00:00
 
  SELECT CAST('2017/12/21' AS DATETIME);
  # 2017-12-21 00:00:00
 
  SELECT CAST('2017.12.21' AS DATETIME);
  # 2017-12-21 00:00:00

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:

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