TO_CHAR - Convert Datetime to String - Oracle to MySQL Migration

In Oracle, TO_CHAR function converts a datetime value to string using the specified format. In MySQL, you can use 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
MONTH	Month name (January - December)	%M
MM	Month (1 - 12)	%m
DY	Abbreviated day (Sun - Sat)	%a
DD	Day (1 - 31)	%d
D	Day of the week (1 - 7)	%w (0 - 6), see notes below
HH24	Hour (0 - 23)	%H
HH or HH12	Hour (1 - 12)	%h
MI	Minutes (0 - 59)	%i
SS	Seconds (0 - 59)	%s

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

Typical conversion examples:

For more information, see Oracle to MySQL Migration.