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:

Oracle TO_DATE MySQL STR_TO_DATE
YYYY 4-digit year %Y
SYYYY 4-digit year with a minus sign for BC dates %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
HH24 Hour (0 - 23) %H
HH or HH12 Hour (1 - 12) %h
MI Minutes (0 - 59) %i
SS Seconds (0 - 59) %s

TO_DATE Conversion Examples

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

Non-Obvious Use Cases

There are some TO_DATE use cases that may look odd at first, but they can still have meaning:

Oracle:

  ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
 
  -- SYSDATE is already DATE, why TO_DATE is used?
  SELECT TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM dual;
 
  -- SYSDATE has the time part, but after TO_DATE it became zero (!) 
  SELECT TO_CHAR(SYSDATE, 'HH24:MI:SS'), 
              TO_CHAR(TO_DATE(SYSDATE, 'DD/MM/YYYY'), 'HH24:MI:SS') FROM dual;
  /* 23:23:48      00:00:00 */

Note that in TO_DATE(SYSDATE, format) expression Oracle firstly converts SYSDATE to string (!) using NLS_DATE_FORMAT and then converts the string back to DATE using the TO_DATE format.

Important note: Oracle can still convert a string to a date successfully, even if the format specified in TO_DATE does not match, as long as the string matches NLS_DATE_FORMAT or some other supported formats:

Oracle:

  -- Format does not match, but Oracle still can convert successfully (!)
  SELECT TO_DATE('14-SEP-2025', 'DD/MM/YYYY') FROM dual;
  /* 14-SEP-25 */
 
  -- Does not match, but also recognized
  SELECT TO_DATE('14.SEP.2025', 'DD/MM/YYYY') FROM dual;
  /* 14-SEP-25 */

Note that STR_TO_DATE(NOW(), format) returns NULL in MySQL if the format does not match '%Y-%m-%d':

MySQL:

  SELECT STR_TO_DATE(NOW(), '%d/%m/%Y');
  /* NULL */
  /* 1 warning */
 
  SHOW WARNINGS;
  /* Warning | 1411 | Incorrect datetime value: '2025-09-16 19:52:34' for function str_to_date */
 
  SELECT STR_TO_DATE(NOW(), '%Y-%m-%d');
  /* 2025-09-16 */

For more information, see Oracle to MySQL Migration.

You could leave a comment if you were logged in.