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 have to use STR_TO_DATE function even in the Oracle Compatibility mode.

Note that the TO_DATE and STR_TO_DATE format strings are different.


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

TO_DATE and STR_TO_DATE Format Specifiers

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

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

Typical conversion examples:

Oracle MariaDB - Oracle Compatibility Mode
1 TO_DATE('2020-10-25', 'YYYY-MM-DD') STR_TO_DATE('2020-10-25', '%Y-%m-%d)
2 TO_DATE('25/10/20', 'DD/MM/RR') STR_TO_DATE('25/10/20','%d/%m/%y')
3 TO_DATE('20201025', 'YYYYMMDD') STR_TO_DATE('20201025', '%Y%m%d')
4 TO_DATE('25/10/2020 21:03', 'DD/MM/YYYY HH24:MI') STR_TO_DATE('25/10/2020 21:03', '%d/%m/%Y %H:%i')

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