TO_DATE - Convert String to Datetime - Oracle to MariaDB Migration

In Oracle, TO_DATE function converts a string value to DATE data type value using the specified format. In MariaDB, you can use 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');

TO_DATE and STR_TO_DATE Format Specifiers

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

Conversion examples:

Oracle MariaDB
1 TO_DATE('2017-12-20', 'YYYY-MM-DD') STR_TO_DATE('2017-12-20', '%Y-%m-%d)
2 TO_DATE('20/12/17', 'DD/MM/RR') STR_TO_DATE('20/12/17','%d/%m/%y')
3 TO_DATE('20171220', 'YYYYMMDD') STR_TO_DATE('20171220', '%Y%m%d')

TO_DATE without Format String

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

For more information, see Oracle to MariaDB Migration.