TO_CHAR - Convert Datetime to String - Oracle to MariaDB Migration

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

In MariaDB, you can use the DATE_FORMAT function. Note that the TO_CHAR and DATE_FORMAT format strings are different.

MariaDB supports the TO_CHAR function starting with version 10.6.1 in both native and Oracle compatibility mode, with support limited to the following format specifiers: YYYY, YY, RRRR, RR, MM, MON, MONTH, MI, DD, DY, DAY, HH, HH12, HH24 and SS.

Oracle:

  -- Convert the current date and time to string (year-month-day)
  SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
  /* 2025-12-26 */

MariaDB:

  -- Convert the current date and time to string (year-month-day)
  SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d');
  /* 2025-12-26 */
 
  -- Since 10.6.1
  SELECT TO_CHAR(SYSDATE(), 'YYYY-MM-DD');
  /* 2025-12-26 */

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	MariaDB TO_CHAR
YYYY		4-digit year	%Y	YYYY
YY		2-digit year	%y	YY
RRRR		2 or 4-digit year, 20th century for 00-49	%Y	RRRR
RR		2-digit year, 20th century for 00-49	%y	RR
MON		Abbreviated month (Jan - Dec)	%b	MON, see notes below
MONTH		Month name (January - December)	%M	MONTH, see notes below
MM		Month (01 - 12)	%m	MM
DY		Abbreviated day (Mon - Sun)	%a	DY, see notes below
DAY		Name of day (Monday - Sunday)	%W	DAY, see notes below
DD		Day (01 - 31)	%d	DD
D		Day of the week (1 - 7)	%w (0 - 6), see notes below
HH24		Hour (00 - 23)	%H	HH24
HH	HH12	Hour (01 - 12)	%h	HH	HH12
MI		Minutes (00 - 59)	%i	MI
SS		Seconds (00 - 59)	%s	SS
SSSSS		Seconds past midnight (0-86399)	Expression, see below
FF	FF6	Microseconds (000000 - 999999)	%f
FF3		Milliseconds (000 - 999)	Expression, see below

Typical conversion examples:

In Oracle, the output of MON, MONTH, DY and DAY formats follows capitalization in the corresponding format element. For example, 'DAY' produces capitalized words like 'SUNDAY', while 'Day' produces 'Sunday' and 'day' produces 'sunday'.

Also, Oracle pads the output with trailing blanks to the width of the longest value - Wednesday for DAY and September for MONTH (American).

Oracle:

  -- Capitalization of format defines capitalization of output
  SELECT TO_CHAR(DATE '2026-01-06', 'day Day DAY') FROM dual;
  /* tuesday   Tuesday   TUESDAY */
 
  -- Note that the value is padded with trailing blanks
  SELECT '-' || TO_CHAR(DATE '2026-01-06', 'day') || '-' FROM dual;
  /* -tuesday  - */
 
  -- FM format specifier removes the padding
  SELECT '-' || TO_CHAR(DATE '2026-01-06', 'fmday') || '-' FROM dual;
  /* -tuesday- */

MariaDB uses only initial capitalization, regardless of the format's capitalization:

MariaDB:

  -- Always initial capitalization
  SELECT TO_CHAR(DATE '2026-01-06', 'day Day DAY');
  /* Tuesday   Tuesday   Tuesday */
 
  -- Note that the value is padded with trailing blanks
  SELECT CONCAT('-', TO_CHAR(DATE '2026-01-06', 'day'), '-');
  /* -Tuesday  - */

Note that MariaDB supports the FM format specifier to suppress the padding since version 12.0.

Note that the day of the week returned by Oracle depends on NLS_TERRITORY setting of the current session and the value's range is 1 to 7 while MariaDB %w range is 0 to 6, and 0 always used for Sunday and 6 for Saturday.

Consider the following example for Sunday, October 6th, 2024:

Oracle:

  ALTER SESSION SET NLS_TERRITORY = 'America';
 
  -- Sunday is 1
  SELECT TO_CHAR(DATE '2024-10-06', 'D') FROM dual; 
  # 1
 
  ALTER SESSION SET NLS_TERRITORY = 'Spain';
 
  -- Sunday is 7 now (week starts on Monday)
  SELECT TO_CHAR(DATE '2024-10-06', 'D') FROM dual; 
  # 7
 
  ALTER SESSION SET NLS_TERRITORY = 'Korea';
 
  -- Sunday is 1 again
  SELECT TO_CHAR(DATE '2024-10-06', 'D') FROM dual;
  # 1

MariaDB offers various way to get the day of the week, and they all return different results:

MariaDB:

  -- For %w Sunday is always 0 (0 = Sunday, 1 = Monday, ... 6 = Saturday)
  SELECT DATE_FORMAT('2024-10-06', '%w'); 
  # 0
 
  -- For DAYOFWEEK Sunday is always 1 (1 = Sunday, 2 = Monday, …, 7 = Saturday) to follow ODBC standard
  SELECT DAYOFWEEK('2024-10-06');
  # 1
 
  -- For WEEKDAY Sunday is always 6 (0 = Monday, 1 = Tuesday, … 6 = Sunday)
  SELECT WEEKDAY('2024-10-06');
  # 6

There are some Oracle TO_CHAR format specifiers that are not directly supported by the DATE_FORMAT function, but you can use various expressions to achieve the same result:

Oracle:

  -- Get milliseconds
  SELECT TO_CHAR(TIMESTAMP '2025-12-24 23:25:57.123456', 'FF3') FROM dual;
  /* 123 */
 
  --  Get seconds past midnight
  SELECT TO_CHAR(TIMESTAMP '2025-12-24 23:25:57.123456', 'SSSSS') FROM dual;
  /* 84357 */
 
  -- Get seconds past midnight and milliseconds
  SELECT TO_CHAR(TIMESTAMP '2025-12-24 23:25:57.123456', 'SSSSSFF3') FROM dual;
  /* 84357123 */

In MariaDB you can use the following expressions:

MariaDB:

  -- Get milliseconds 
  SELECT SUBSTR(DATE_FORMAT('2025-12-24 23:25:57.123456', '%f'), 1, 3);
  /* 123 */
 
  --  Get seconds past midnight
  SELECT CAST(CAST(TIME_TO_SEC('2025-12-24 23:25:57.123456') AS UNSIGNED) AS CHAR);
  /* 84357 */
 
  -- Get seconds past midnight and milliseconds
  SELECT CONCAT(
     CAST(CAST(TIME_TO_SEC('2025-12-24 23:25:57.123456') AS UNSIGNED) AS CHAR),
     SUBSTR(DATE_FORMAT('2025-12-24 23:25:57.123456', '%f'), 1, 3)
     );
  /* 84357123 */

For more information, see Oracle to MariaDB Migration.