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');

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:

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

For more information, see Oracle to MariaDB Migration.