Convert String to DateTime – Oracle TO_DATE to MySQL STR_TO_DATE Migration

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_DATEDescription MySQL: STR_TO_DATE
YYYY4-digit year%Y
YY2-digit year%y
RRRR2 or 4-digit year, 20th century for 00-49%Y
RR2-digit year, 20th century for 00-49%y
MONAbbreviated month (Jan – Dec)%b
MONTHMonth name (January – December)%M
MMMonth (1 – 12)%m
DYAbbreviated day (Sun – Sat)%a
DDDay (1 – 31)%d
HH24Hour (0 – 23)%H
HH or HH12Hour (1 – 12)%h
MIMinutes (0 – 59)%i
SSSeconds (0 – 59)%s
Date Format Specifiers Mapping for TO_DATE to STR_TO_DATE

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

Default image
neotam
Naveen T aka neotam. Programming language agnostic, Software architect, Python expert, Networking & DevOps engineer & consultant with 7+ years of experience in creating serious web applications, real time event-driven non blocking applications and database driven applications ranging from small scale to enterprise grade. website
Leave a Reply