TO_DATE - Convert String to Datetime - Oracle to MariaDB Migration

In Oracle, TO_DATE function converts a string value to DATE data type value using the specified format. In MariaDB, you have to use STR_TO_DATE function even in the Oracle Compatibility mode.

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('2020-10-25', 'YYYY-MM-DD') FROM dual;

MariaDB - Oracle Compatibility:

  -- Specify a datetime string literal and its exact format
  SELECT STR_TO_DATE('2020-10-25', '%Y-%m-%d');

TO_DATE and STR_TO_DATE Format Specifiers

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

Oracle TO_DATE		MariaDB 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

Conversion Examples

Typical conversion examples:

	Oracle	MariaDB - Oracle Compatibility Mode
1	TO_DATE('2020-10-25', 'YYYY-MM-DD')	STR_TO_DATE('2020-10-25', '%Y-%m-%d)
2	TO_DATE('25/10/20', 'DD/MM/RR')	STR_TO_DATE('25/10/20','%d/%m/%y')
3	TO_DATE('20201025', 'YYYYMMDD')	STR_TO_DATE('20201025', '%Y%m%d')
4	TO_DATE('25/10/2020 21:03', 'DD/MM/YYYY HH24:MI')	STR_TO_DATE('25/10/2020 21:03', '%d/%m/%Y %H:%i')

For more information, see Oracle to MariaDB Migration - Oracle Compatibility Mode.

SQLines SQL Converter

SQLines Data

Migration to MariaDB

Migration to Oracle

Migration to SQL Server

Migration to MySQL

Migration to PostgreSQL

Migration to SingleStore

Migration to IBM DB2

Migration to Redshift

Migration to Snowflake

Migration to Apache Hive

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to EsgynDB

Application Conversion

Database Reference

TO_DATE - Convert String to Datetime - Oracle to MariaDB Migration

TO_DATE and STR_TO_DATE Format Specifiers

Conversion Examples