Oracle database is owned by Oracle company while MySQL used to be open source and it is acquired by Oracle such that both Oracle database and MySQL are owned by Oracle corporation yet both seem different when they are used. Event after acquisition MySQL continued to be open source and Oracle database is proprietary database management software. Among many differences Oracle supports SQL and PL/SQL while MySQL supports only SQL

To convert the given string in a specified format to Date data type we have STR_TO_DATE in MySQL and TO_DATE function in Oracle. How these two functions operate is different according to Syntax

Oracle TO_DATE Syntax

TO_DATE(char [, fmt [, 'nlsparam' ] ])

TO_DATE converts the given char of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2 to value of type DATE.

EXAMPLE:

Use of TO_DATE in INSERT statement

INSERT INTO CABS VALUES ('008329', to_date('04/11/2019 17:10:00', 'dd/mm/yyyy hh24:mi:ss'), to_date('04/11/2019 12:25:00', 'dd/mm/yyyy hh24:mi:ss') );

Example which demonstrates the conversion of String to DATE

SELECT TO_DATE(
    'January 15, 1999, 10:00 A.M.',
    'Month dd, YYYY, HH:MI A.M.',
     'NLS_DATE_LANGUAGE = American')
     FROM DUAL;

Other functions related to TO_DATE in Oracle are TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_DSINTERVAL, AND TO_YMINTERVAL

MySQL STR_TO_DATE function

STR_TO_DATE is the opposite of DATE_FORMAT where STR_TO_DATE function converts given string in the specified format to DATE type where as DATE_FORMAT performs the opposite

Example:

mysql> SELECT STR_TO_DATE('01,5,2022','%d,%m,%Y');
        -> '2022-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2022','%M %d,%Y');
        -> '2022-05-01'

If invalid date that doesn’t match the given format, STR_TO_DATE return null

mysql> SELECT STR_TO_DATE('ffac:40:27','%h:%i:%s');
        -> NULL

When migrating from Oracle to MySQL/MariaDB, TO_DATE function needs to be replaced with STR_TO_DATE and map the format specifiers as follows

Oracle: TO_DATE	Description	MySQL: STR_TO_DATE
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
HH24	Hour (0 – 23)	%H
HH or HH12	Hour (1 – 12)	%h
MI	Minutes (0 – 59)	%i
SS	Seconds (0 – 59)	%s

Let’s check following examples

Convert the given date “2022-04-11” to DATE

Oracle

TO_DATE('2022-04-11', 'YYYY-MM-DD')

MySQL

STR_TO_DATE('2022-04-11', '%Y-%m-%d')