TO_CHAR - Convert Datetime to String - Oracle to MySQL Migration

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

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

Oracle:

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

MySQL:

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

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

Oracle TO_CHAR			MySQL 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, see notes below
MONTH		Month name (January - December)	%M, see notes below
MM		Month (01 - 12)	%m
DY		Abbreviated day (Mon - Sun)	%a, see notes below
DAY		Name of day (Monday - Sunday)	%W, see notes below
DD		Day (01 - 31)	%d
D		Day of the week (1 - 7)	%w (0 - 6), see notes below
HH24		Hour (00 - 23)	%H
HH	HH12	Hour (01 - 12)	%h
MI		Minutes (00 - 59)	%i
SS		Seconds (00 - 59)	%s
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- */

MySQL uses only initial capitalization and does not pad the output with trailing blanks:

MySQL:

  -- Always initial capitalization
  SELECT DATE_FORMAT(DATE '2026-01-06', '%W');
  /* Tuesday */
 
  -- The value is not padded with trailing blanks
  SELECT CONCAT('-', DATE_FORMAT(DATE '2026-01-06', '%W'), '-');
  /* -Tuesday- */

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 MySQL %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

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

MySQL:

  -- 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 MySQL you can use the following expressions:

MySQL:

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

For more information, see Oracle to MySQL Migration.