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

TO_CHAR and DATE_FORMAT Format Specifiers

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

Typical conversion examples:

	Oracle	MySQL
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')

SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.

You could leave a comment if you were logged in.