TO_CHAR - Convert Datetime to String - Oracle to MariaDB Migration

In Oracle, TO_CHAR function converts a datetime value to string using the specified format.

In MariaDB before 10.6.1, you have to use DATE_FORMAT function even in the Oracle Compatibility mode. Note that the TO_CHAR and DATE_FORMAT format strings are different.

MariaDB supports TO_CHAR function since version 10.6.1

Oracle:

  -- Convert the current date and time to string (year-month-day)
  SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
  # 2020-10-25

MariaDB - Before 10.6,1, Oracle Compatibility:

  -- Convert the current date and time to string (year-month-day)
  SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d');
  # 2020-10-25

TO_CHAR and DATE_FORMAT Format Specifiers

When you convert Oracle TO_CHAR function to DATE_FORMAT function in MariaDB, you have to map the format specifiers:

Oracle TO_CHAR MariaDB DATE_FORMAT
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
1 TO_CHAR(SYSDATE, 'YYYY-MM-DD') DATE_FORMAT(SYSDATE(), '%Y-%m-%d)
2 TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%s')
3 TO_CHAR(SYSDATE, 'DD-MON-YYYY') DATE_FORMAT(SYSDATE(), '%d-%b-%Y')
4 TO_CHAR(SYSDATE, 'RRRR-MM-DD') DATE_FORMAT(SYSDATE(), '%Y-%m-%d')
5 TO_CHAR(SYSDATE, ''YYYYMMDDHH24MI' DATE_FORMAT(SYSDAYE(), '%Y%m%d%H%i'

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