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

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.