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