TO_DATE - Convert String to Datetime - Oracle to MySQL Migration

In Oracle, TO_DATE function converts a string value to DATE data type value using the specified format. In MySQL, you can use STR_TO_DATE function.

Note that the TO_DATE and STR_TO_DATE format strings are different.

Oracle:

  -- Specify a datetime string literal and its exact format
  SELECT TO_DATE('2013-02-11', 'YYYY-MM-DD') FROM dual;

MySQL:

  -- Specify a datetime string literal and its exact format
  SELECT STR_TO_DATE('2013-02-11', '%Y-%m-%d');

TO_DATE and STR_TO_DATE Format Specifiers

When you convert Oracle TO_DATE function to STR_TO_DATE function in MySQL, you have to map the format specifiers:

Typical conversion examples:

	Oracle	MySQL
1	TO_DATE('2013-02-11', 'YYYY-MM-DD')	STR_TO_DATE('2013-02-11', '%Y-%m-%d)
2	TO_DATE('11/02/13', 'DD/MM/RR')	STR_TO_DATE('11/02/13','%d/%m/%y')
3	TO_DATE('20140924', 'YYYYMMDD') STR_TO_DATE('20140924', '%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.