TO_CHAR - Convert Datetime to String - Oracle to MariaDB Migration

In Oracle, TO_CHAR function converts a datetime value to string using the specified format.

In MariaDB before 10.6.1, you have to use DATE_FORMAT function even in the Oracle Compatibility mode. Note that the TO_CHAR and DATE_FORMAT format strings are different.

MariaDB supports TO_CHAR function since version 10.6.1

Oracle:

  -- Convert the current date and time to string (year-month-day)
  SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD') FROM dual;
  # 2020-10-25

MariaDB - Before 10.6,1, Oracle Compatibility:

  -- Convert the current date and time to string (year-month-day)
  SELECT DATE_FORMAT(SYSDATE(), '%Y-%m-%d');
  # 2020-10-25

TO_CHAR and DATE_FORMAT Format Specifiers

When you convert Oracle TO_CHAR function to DATE_FORMAT function in MariaDB, you have to map the format specifiers:

Oracle TO_CHAR		MariaDB DATE_FORMAT
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
1	TO_CHAR(SYSDATE, 'YYYY-MM-DD')	DATE_FORMAT(SYSDATE(), '%Y-%m-%d)
2	TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS')	DATE_FORMAT(SYSDATE(), '%Y%m%d%H%i%s')
3	TO_CHAR(SYSDATE, 'DD-MON-YYYY')	DATE_FORMAT(SYSDATE(), '%d-%b-%Y')
4	TO_CHAR(SYSDATE, 'RRRR-MM-DD')	DATE_FORMAT(SYSDATE(), '%Y-%m-%d')
5	TO_CHAR(SYSDATE, ''YYYYMMDDHH24MI'	DATE_FORMAT(SYSDAYE(), '%Y%m%d%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 IBM DB2

Migration to Redshift

Migration to Databricks

Migration to Snowflake

Migration to Spark

Migration to Presto/Trino

Migration to Netezza

Migration to Greenplum

Migration to SingleStore

Application Conversion

Database Reference

TO_CHAR - Convert Datetime to String - Oracle to MariaDB Migration

TO_CHAR and DATE_FORMAT Format Specifiers

Conversion Examples