MDEV-20017 Implement TO_CHAR() Oracle compatible function

TO_CHAR(expr, fmt)
- expr: required parameter, data/time/timestamp type expression
- fmt: optional parameter, format string, supports
  YYYY/YYY/YY/RRRR/RR/MM/MON/MONTH/MI/DD/DY/HH/HH12/HH24/SS and special
  characters. The default value is "YYYY-MM-DD HH24:MI:SS"

In Oracle, TO_CHAR() can also be used to convert numbers to strings, but
this is not supported. This will gave an error in this patch.

Other things:
- If format strings is a constant, it's evaluated only once and if there
  is any errors in it, they are given at once and the statement will abort.

Original author: woqutech
Lots of optimizations and cleanups done as part of review

9 files changed, 1591 insertions, 1 deletions

diff --git a/mysql-test/suite/compat/README.txt b/mysql-test/suite/compat/README.txt
new file mode 100644
index 00000000000..b1a2033f6e2
--- /dev/null
+++ b/mysql-test/suite/compat/README.txt
@@ -0,0 +1,7 @@
+To run a test suite under this directory, you should use the format:
+
+mysql-test-run --suite=compat/oracle
+
+or to run one test:
+
+mysql-test-run compat/oracle.test_name
diff --git a/mysql-test/suite/compat/oracle/r/func_to_char.result b/mysql-test/suite/compat/oracle/r/func_to_char.result
new file mode 100644
index 00000000000..56009d88400
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/r/func_to_char.result
@@ -0,0 +1,441 @@
+set @save_sql_mode=@@sql_mode;
+#
+# test for datetime
+#
+CREATE TABLE t_to_char1(c0 int, c1 date, c2 time, c3 datetime);
+INSERT INTO t_to_char1 VALUES (1, '1000-1-1', '00:00:00', '1000-1-1 00:00:00');
+INSERT INTO t_to_char1 VALUES (2, '9999-12-31', '23:59:59', '9999-12-31 23:59:59');
+INSERT INTO t_to_char1 VALUES (3, '2021-01-03', '08:30:00', '2021-01-03 08:30:00');
+INSERT INTO t_to_char1 VALUES (4, '2021-07-03', '18:30:00', '2021-07-03 18:30:00');
+CREATE TABLE t_to_char2(c1 timestamp);
+INSERT INTO t_to_char2 VALUES ('1980-01-11 04:50:39');
+INSERT INTO t_to_char2 VALUES ('2000-11-11 12:50:00');
+INSERT INTO t_to_char2 VALUES ('2030-11-11 18:20:10');
+SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char2;
+TO_CHAR(c1, 'YYYY-MM-DD')
+1980-01-11
+2000-11-11
+2030-11-11
+SELECT TO_CHAR(c1, 'HH24-MI-SS') FROM t_to_char2;
+TO_CHAR(c1, 'HH24-MI-SS')
+04-50-39
+12-50-00
+18-20-10
+#
+# test YYYY/YY/MM/DD/HH/HH24/MI/SS
+#
+SELECT TO_CHAR(c1, 'YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+1000-01-01	12:00:00	00-01-01 00:00:00
+9999-12-31	11:59:59	99-12-31 23:59:59
+2021-01-03	08:30:00	21-01-03 08:30:00
+2021-07-03	06:30:00	21-07-03 18:30:00
+SELECT TO_CHAR(c1, 'yyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'yy-mm-dd hh24:mi:ss') AS C3 FROM t_to_char1;
+C1	C2	C3
+1000-01-01	12:00:00	00-01-01 00:00:00
+9999-12-31	11:59:59	99-12-31 23:59:59
+2021-01-03	08:30:00	21-01-03 08:30:00
+2021-07-03	06:30:00	21-07-03 18:30:00
+#
+# test YYY/Y/MON/DD/DY/HH/HH12/MI/SS
+#
+SELECT TO_CHAR(c1, 'YYY-MON-DD') AS C1, TO_CHAR(c2, 'HH12:MI:SS') AS C2, TO_CHAR(c3, 'Y-MONTH-DY HH:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+000-Jan-01	12:00:00	0-January  -Wed 12:00:00
+999-Dec-31	11:59:59	9-December -Fri 11:59:59
+021-Jan-03	08:30:00	1-January  -Sun 08:30:00
+021-Jul-03	06:30:00	1-July     -Sat 06:30:00
+SELECT TO_CHAR(c1, 'yyy-Mon-Dd') AS C1, TO_CHAR(c2, 'Hh12:mi:Ss') AS C2, TO_CHAR(c3, 'y-Month-Dy Hh:Mi:Ss') AS C3 FROM t_to_char1;
+C1	C2	C3
+000-Jan-01	12:00:00	0-January  -Wed 12:00:00
+999-Dec-31	11:59:59	9-December -Fri 11:59:59
+021-Jan-03	08:30:00	1-January  -Sun 08:30:00
+021-Jul-03	06:30:00	1-July     -Sat 06:30:00
+#
+# test RRRR/RR/DAY
+#
+SELECT TO_CHAR(c1, 'RRRR-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'RRRR-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+1000-01-01	12:00:00	1000-01-01 00:00:00
+9999-12-31	11:59:59	9999-12-31 23:59:59
+2021-01-03	08:30:00	2021-01-03 08:30:00
+2021-07-03	06:30:00	2021-07-03 18:30:00
+SELECT TO_CHAR(c1, 'RR-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+00-01-01	12:00:00	00-01-01 00:00:00
+99-12-31	11:59:59	99-12-31 23:59:59
+21-01-03	08:30:00	21-01-03 08:30:00
+21-07-03	06:30:00	21-07-03 18:30:00
+SELECT TO_CHAR(c1, 'Rrrr-Mm-Dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'Rrrr-mm-dd Hh24:mi:ss') AS C3 FROM t_to_char1;
+C1	C2	C3
+1000-01-01	12:00:00	1000-01-01 00:00:00
+9999-12-31	11:59:59	9999-12-31 23:59:59
+2021-01-03	08:30:00	2021-01-03 08:30:00
+2021-07-03	06:30:00	2021-07-03 18:30:00
+SELECT TO_CHAR(c1, 'rr-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'yy-mm-dd hh24:Mi:ss') AS C3 FROM t_to_char1;
+C1	C2	C3
+00-01-01	12:00:00	00-01-01 00:00:00
+99-12-31	11:59:59	99-12-31 23:59:59
+21-01-03	08:30:00	21-01-03 08:30:00
+21-07-03	06:30:00	21-07-03 18:30:00
+#
+# test AD/A.D./BC/B.C./AM/A.M./PM/P.M.
+#
+SELECT TO_CHAR(c1, 'ADYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'AD.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+AD1000-01-01	12:00:00	AD.1000-01-01 00:00:00
+AD9999-12-31	11:59:59	AD.9999-12-31 23:59:59
+AD2021-01-03	08:30:00	AD.2021-01-03 08:30:00
+AD2021-07-03	06:30:00	AD.2021-07-03 18:30:00
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+A.D.1000-01-01	12:00:00	A.D..1000-01-01 00:00:00
+A.D.9999-12-31	11:59:59	A.D..9999-12-31 23:59:59
+A.D.2021-01-03	08:30:00	A.D..2021-01-03 08:30:00
+A.D.2021-07-03	06:30:00	A.D..2021-07-03 18:30:00
+SELECT TO_CHAR(c1, 'ADYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'AD.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+AD1000-01-01	12:00:00	AD.1000-01-01 00:00:00
+AD9999-12-31	11:59:59	AD.9999-12-31 23:59:59
+AD2021-01-03	08:30:00	AD.2021-01-03 08:30:00
+AD2021-07-03	06:30:00	AD.2021-07-03 18:30:00
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+A.D.1000-01-01	12:00:00	A.D..1000-01-01 00:00:00
+A.D.9999-12-31	11:59:59	A.D..9999-12-31 23:59:59
+A.D.2021-01-03	08:30:00	A.D..2021-01-03 08:30:00
+A.D.2021-07-03	06:30:00	A.D..2021-07-03 18:30:00
+SELECT TO_CHAR(c1, 'BCYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'BCYYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+AD1000-01-01	12:00:00	AD1000-01-01 00:00:00
+AD9999-12-31	11:59:59	AD9999-12-31 23:59:59
+AD2021-01-03	08:30:00	AD2021-01-03 08:30:00
+AD2021-07-03	06:30:00	AD2021-07-03 18:30:00
+SELECT TO_CHAR(c1, 'B.C.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'B.C.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+A.D.1000-01-01	12:00:00	A.D.1000-01-01 00:00:00
+A.D.9999-12-31	11:59:59	A.D.9999-12-31 23:59:59
+A.D.2021-01-03	08:30:00	A.D.2021-01-03 08:30:00
+A.D.2021-07-03	06:30:00	A.D.2021-07-03 18:30:00
+SELECT TO_CHAR(c1, 'bcyyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'BcYYyy-MM-DD Hh24:mi:sS') AS C3 FROM t_to_char1;
+C1	C2	C3
+AD1000-01-01	12:00:00	AD1000-01-01 00:00:00
+AD9999-12-31	11:59:59	AD9999-12-31 23:59:59
+AD2021-01-03	08:30:00	AD2021-01-03 08:30:00
+AD2021-07-03	06:30:00	AD2021-07-03 18:30:00
+SELECT TO_CHAR(c1, 'b.c.yyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mI:Ss') AS C2, TO_CHAR(c3, 'b.C.Yyyy-Mm-dd hH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+A.D.1000-01-01	12:00:00	A.D.1000-01-01 00:00:00
+A.D.9999-12-31	11:59:59	A.D.9999-12-31 23:59:59
+A.D.2021-01-03	08:30:00	A.D.2021-01-03 08:30:00
+A.D.2021-07-03	06:30:00	A.D.2021-07-03 18:30:00
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'PMHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD P.M.HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+A.D.1000-01-01	AM12:00:00	A.D..1000-01-01 A.M.00:00:00
+A.D.9999-12-31	PM11:59:59	A.D..9999-12-31 P.M.23:59:59
+A.D.2021-01-03	AM08:30:00	A.D..2021-01-03 A.M.08:30:00
+A.D.2021-07-03	PM06:30:00	A.D..2021-07-03 P.M.18:30:00
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'pmHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD p.m.HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+A.D.1000-01-01	AM12:00:00	A.D..1000-01-01 A.M.00:00:00
+A.D.9999-12-31	PM11:59:59	A.D..9999-12-31 P.M.23:59:59
+A.D.2021-01-03	AM08:30:00	A.D..2021-01-03 A.M.08:30:00
+A.D.2021-07-03	PM06:30:00	A.D..2021-07-03 P.M.18:30:00
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'AMHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD A.m.HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+A.D.1000-01-01	AM12:00:00	A.D..1000-01-01 A.M.00:00:00
+A.D.9999-12-31	PM11:59:59	A.D..9999-12-31 P.M.23:59:59
+A.D.2021-01-03	AM08:30:00	A.D..2021-01-03 A.M.08:30:00
+A.D.2021-07-03	PM06:30:00	A.D..2021-07-03 P.M.18:30:00
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'amHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD a.M.HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+A.D.1000-01-01	AM12:00:00	A.D..1000-01-01 A.M.00:00:00
+A.D.9999-12-31	PM11:59:59	A.D..9999-12-31 P.M.23:59:59
+A.D.2021-01-03	AM08:30:00	A.D..2021-01-03 A.M.08:30:00
+A.D.2021-07-03	PM06:30:00	A.D..2021-07-03 P.M.18:30:00
+#
+# test format without order
+#
+SELECT TO_CHAR(c1, 'MM-YYYY-DD') AS C1, TO_CHAR(c2, 'HH:SS:MI') AS C2, TO_CHAR(c3, 'DD-YY-MM MI:SS:HH24') AS C3 FROM t_to_char1;
+C1	C2	C3
+01-1000-01	12:00:00	01-00-01 00:00:00
+12-9999-31	11:59:59	31-99-12 59:59:23
+01-2021-03	08:00:30	03-21-01 30:00:08
+07-2021-03	06:00:30	03-21-07 30:00:18
+SELECT TO_CHAR(c1, 'yyy-Dd-Mon') AS C1, TO_CHAR(c2, 'mi:Hh12:Ss') AS C2, TO_CHAR(c3, 'Ss:Hh:Mi Dy-y-Month') AS C3 FROM t_to_char1;
+C1	C2	C3
+000-01-Jan	00:12:00	00:12:00 Wed-0-January  
+999-31-Dec	59:11:59	59:11:59 Fri-9-December 
+021-03-Jan	30:08:00	00:08:30 Sun-1-January  
+021-03-Jul	30:06:00	00:06:30 Sat-1-July     
+SELECT TO_CHAR(c1, 'Dd-Mm-Rrrr') AS C1, TO_CHAR(c2, 'ss:hh:mi') AS C2, TO_CHAR(c3, 'ss:Rrrr-hh24-dd mon:mi') AS C3 FROM t_to_char1;
+C1	C2	C3
+01-01-1000	00:12:00	00:1000-00-01 Jan:00
+31-12-9999	59:11:59	59:9999-23-31 Dec:59
+03-01-2021	00:08:30	00:2021-08-03 Jan:30
+03-07-2021	00:06:30	00:2021-18-03 Jul:30
+SELECT TO_CHAR(c1, 'YYYYA.D.-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+1000A.D.-01-01	12:00:00	A.D..1000-01-01 00:00:00
+9999A.D.-12-31	11:59:59	A.D..9999-12-31 23:59:59
+2021A.D.-01-03	08:30:00	A.D..2021-01-03 08:30:00
+2021A.D.-07-03	06:30:00	A.D..2021-07-03 18:30:00
+#
+# test for special characters
+#
+SELECT TO_CHAR(c1, 'YYYYMMDD') AS C1, TO_CHAR(c2, 'HHMISS') AS C2, TO_CHAR(c3, 'YYMMDDHH24MISS') AS C3 FROM t_to_char1;
+C1	C2	C3
+10000101	120000	000101000000
+99991231	115959	991231235959
+20210103	083000	210103083000
+20210703	063000	210703183000
+SELECT TO_CHAR(c1, 'YYYY!!MM@DD') AS C1, TO_CHAR(c2, 'HH#MI$SS') AS C2, TO_CHAR(c3, 'YY%MM^DD*HH24(MI)SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+1000!!01@01	12#00$00	00%01^01*00(00)00
+9999!!12@31	11#59$59	99%12^31*23(59)59
+2021!!01@03	08#30$00	21%01^03*08(30)00
+2021!!07@03	06#30$00	21%07^03*18(30)00
+SELECT TO_CHAR(c1, 'YYYY_MM+DD') AS C1, TO_CHAR(c2, 'HH=MI{SS') AS C2, TO_CHAR(c3, 'YY}MMDDHH24MISS') AS C3 FROM t_to_char1;
+C1	C2	C3
+1000_01+01	12=00{00	00}0101000000
+9999_12+31	11=59{59	99}1231235959
+2021_01+03	08=30{00	21}0103083000
+2021_07+03	06=30{00	21}0703183000
+SELECT TO_CHAR(c1, 'YYYY,MM.DD') AS C1, TO_CHAR(c2, 'HH/MI;SS') AS C2, TO_CHAR(c3, 'YY>MM<DD]HH24[MI\SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+1000,01.01	12/00;00	00>01<01]00[0000
+9999,12.31	11/59;59	99>12<31]23[5959
+2021,01.03	08/30;00	21>01<03]08[3000
+2021,07.03	06/30;00	21>07<03]18[3000
+SELECT TO_CHAR(c1, 'YYYY||||MM|DD') AS C1, TO_CHAR(c2, 'HH&|MI|&|SS') AS C2, TO_CHAR(c3, 'YY&&&\\MM|&&|DD HH24|| MI&||"abx"|SS') AS C3 FROM t_to_char1;
+C1	C2	C3
+1000|||0101	12&|00&|00	00&&&\01&&|01 00| 00&||abx00
+9999|||1231	11&|59&|59	99&&&\12&&|31 23| 59&||abx59
+2021|||0103	08&|30&|00	21&&&\01&&|03 08| 30&||abx00
+2021|||0703	06&|30&|00	21&&&\07&&|03 18| 30&||abx00
+SELECT TO_CHAR(c1, 'YYYY&MM-DD') FROM t_to_char1 where c0=1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at &MM-DD
+SELECT TO_CHAR(c1, 'YYYY"abx"MM"bsz"DD') AS C1 FROM t_to_char1;
+C1
+1000abx01bsz01
+9999abx12bsz31
+2021abx01bsz03
+2021abx07bsz03
+#
+# test for other locale
+#
+SET character_set_client='utf8';
+SET character_set_connection='utf8';
+SET character_set_results='utf8';
+SET lc_time_names='zh_TW';
+SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
+TO_CHAR(c1, 'YYYY-MON-DAY')
+1000- 1月-週三
+9999-12月-週五
+2021- 1月-週日
+2021- 7月-週六
+SET lc_time_names='de_DE';
+SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
+TO_CHAR(c1, 'YYYY-MON-DAY')
+1000-Jan-Mittwoch  
+9999-Dez-Freitag   
+2021-Jan-Sonntag   
+2021-Jul-Samstag   
+SET lc_time_names='en_US';
+SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
+TO_CHAR(c1, 'YYYY-MON-DAY')
+1000-Jan-Wednesday
+9999-Dec-Friday   
+2021-Jan-Sunday   
+2021-Jul-Saturday 
+SET lc_time_names='zh_CN';
+SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
+TO_CHAR(c1, 'YYYY-MON-DAY')
+1000- 1月-星期三
+9999-12月-星期五
+2021- 1月-星期日
+2021- 7月-星期六
+#
+# test for invalid format
+#
+SELECT TO_CHAR(c1, 'YYYYaxMON-DAY') FROM t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at axMON-DA
+SELECT TO_CHAR(c1, 'YYYY\nMON-DAY') FROM t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at 
+MON-DAY
+SELECT TO_CHAR(c1, 'YYYY\rMON-DAY') FROM t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at 
MON-DAY
+SELECT TO_CHAR(c1, 'YYYY分隔MON-DAY') FROM t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at 分隔MO
+SELECT TO_CHAR(c1, 'YYYY-分隔MON-DAY') FROM t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at 分隔MO
+select to_char(c3, 'YYYYxDDD') from t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at xDDD
+select to_char(c3, 'YYYY&DDD') from t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at &DDD
+select to_char(c3, 'xxYYYY-DD') from t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at xxYYYY-D
+SET character_set_client='latin1';
+SET character_set_connection='latin1';
+SET character_set_results='latin1';
+#
+# test for unusual format
+#
+select to_char(c3, 'YYYYYYYYYYYYYYY') from t_to_char1;
+to_char(c3, 'YYYYYYYYYYYYYYY')
+100010001000000
+999999999999999
+202120212021021
+202120212021021
+select to_char(c3, 'YYYYYYYYYYYYYYYDDDDDD') from t_to_char1;
+to_char(c3, 'YYYYYYYYYYYYYYYDDDDDD')
+100010001000000010101
+999999999999999313131
+202120212021021030303
+202120212021021030303
+#
+# oracle max length is 144
+#
+select to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY') from t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: datetime format string is too long
+CREATE TABLE t_f(c1 varchar(150));
+insert into t_f values('YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD');
+select to_char('2000-11-11', c1) from t_f;
+to_char('2000-11-11', c1)
+NULL
+Warnings:
+Warning	4175	Oracle compatibility function error: datetime format string is too long
+DROP TABLE t_f;
+select to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD-MM') from t_to_char1 where c0 = 1;
+to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD-MM')
+100010001000100010001000100010001000100010001000100010001000100010001000100010001000100010001000100010001000100010001000-01-01
+#
+# now only support two parameter.
+#
+select to_char(c3) from t_to_char1 where c0 =1;
+to_char(c3)
+1000-01-01 00:00:00
+select to_char(c3, "YYYY-MM-DD HH:MI:SS") from t_to_char1 where c0 =1;
+to_char(c3, "YYYY-MM-DD HH:MI:SS")
+1000-01-01 12:00:00
+select to_char(c3, "YYYY-MM-DD HH:MI:SS", "zh_CN") from t_to_char1 where c0 = 1;
+ERROR 42000: Incorrect parameter count in the call to native function 'to_char'
+select to_char(c3, "YYYY-MM-DD HH:MI:SS", "NLS_DATE_LANGUAGE = zh_CN") from t_to_char1 where c0 = 1;
+ERROR 42000: Incorrect parameter count in the call to native function 'to_char'
+#
+# oracle support format but mariadb does not support
+#
+select to_char(c3, 'DDD') from t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at D
+select to_char(c3, 'D') from t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at D
+select to_char(c3, 'DS') from t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at DS
+select to_char(c3, 'IY') from t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at IY
+select to_char(c3, 'IYYY') from t_to_char1 where c0 = 1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at IYYY
+#
+# test for first argument data type
+# 
+select to_char(1, 'yyyy');
+ERROR HY000: Oracle compatibility function error: data type of first argument must be type date/datetime/time or string
+select to_char(1.1, 'yyyy');
+ERROR HY000: Oracle compatibility function error: data type of first argument must be type date/datetime/time or string
+CREATE TABLE t_a(c1 int, c2 float, c3 decimal, c4 char(20), c5 varchar(20), c6 nchar(20), c7 nvarchar(20));
+insert into t_a VALUES (1, 3.2, 2002.02, '2000-11-11', '2000-11-11', '2000-11-11', '2000-11-11');
+Warnings:
+Note	1265	Data truncated for column 'c3' at row 1
+SELECT TO_CHAR(c1, 'YYYY') from t_a;
+ERROR HY000: Oracle compatibility function error: data type of first argument must be type date/datetime/time or string
+SELECT TO_CHAR(c2, 'YYYY') from t_a;
+ERROR HY000: Oracle compatibility function error: data type of first argument must be type date/datetime/time or string
+SELECT TO_CHAR(c3, 'YYYY') from t_a;
+ERROR HY000: Oracle compatibility function error: data type of first argument must be type date/datetime/time or string
+SELECT TO_CHAR(c4, 'YYYY') from t_a;
+TO_CHAR(c4, 'YYYY')
+2000
+SELECT TO_CHAR(c5, 'YYYY') from t_a;
+TO_CHAR(c5, 'YYYY')
+2000
+SELECT TO_CHAR(c6, 'YYYY') from t_a;
+TO_CHAR(c6, 'YYYY')
+2000
+SELECT TO_CHAR(c7, 'YYYY') from t_a;
+TO_CHAR(c7, 'YYYY')
+2000
+DROP TABLE t_a;
+CREATE TABLE t_b(c0 int, c1 char(20), c2 varchar(20), c3 nchar(20), c4 nvarchar(20));
+INSERT INTO t_b VALUES (1111, 'YYYY-MM-DD', 'YYYY-MM-DD', 'YYYY-MM-DD', 'YYYY-MM-DD');
+SELECT TO_CHAR('2000-11-11', c0) FROM t_b;
+TO_CHAR('2000-11-11', c0)
+NULL
+Warnings:
+Warning	4175	Oracle compatibility function error: date format not recognized at 1111
+SELECT TO_CHAR('2000-11-11', c1) FROM t_b;
+TO_CHAR('2000-11-11', c1)
+2000-11-11
+SELECT TO_CHAR('2000-11-11', c2) FROM t_b;
+TO_CHAR('2000-11-11', c2)
+2000-11-11
+SELECT TO_CHAR('2000-11-11', c3) FROM t_b;
+TO_CHAR('2000-11-11', c3)
+2000-11-11
+SELECT TO_CHAR('2000-11-11', c4) FROM t_b;
+TO_CHAR('2000-11-11', c4)
+2000-11-11
+DROP TABLE t_b;
+EXPLAIN EXTENDED SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
+1	SIMPLE	t_to_char1	ALL	NULL	NULL	NULL	NULL	4	100.00	
+Warnings:
+Note	1003	select to_char(`test`.`t_to_char1`.`c1`,'YYYY-MM-DD') AS `TO_CHAR(c1, 'YYYY-MM-DD')` from `test`.`t_to_char1`
+#
+# test for time type with date format string
+#
+SELECT TO_CHAR(c2, 'YYYY-MM-DD HH:MI:SS') from t_to_char1;
+TO_CHAR(c2, 'YYYY-MM-DD HH:MI:SS')
+0000-00-00 12:00:00
+0000-00-00 11:59:59
+0000-00-00 08:30:00
+0000-00-00 06:30:00
+SELECT TO_CHAR(c2, 'YYYY-MON-DY HH:MI:SS') from t_to_char1;
+TO_CHAR(c2, 'YYYY-MON-DY HH:MI:SS')
+0000-00-00 12:00:00
+0000-00-00 11:59:59
+0000-00-00 08:30:00
+0000-00-00 06:30:00
+SELECT TO_CHAR(c2, 'MON-YYYY-DY HH:MI:SS') from t_to_char1;
+TO_CHAR(c2, 'MON-YYYY-DY HH:MI:SS')
+00-0000-00 12:00:00
+00-0000-00 11:59:59
+00-0000-00 08:30:00
+00-0000-00 06:30:00
+SELECT TO_CHAR(c2, 'YYYY-MONTH-DAY HH:MI:SS') from t_to_char1;
+TO_CHAR(c2, 'YYYY-MONTH-DAY HH:MI:SS')
+0000-00-00 12:00:00
+0000-00-00 11:59:59
+0000-00-00 08:30:00
+0000-00-00 06:30:00
+DROP TABLE t_to_char1;
+DROP TABLE t_to_char2;
+#
+# Test strict mode
+#
+create table t1 (a datetime, b int, f varchar(30)) engine=myisam;
+insert into t1 values ("2021-01-24 19:22:10", 2014, "YYYY-MM-DD");
+insert into t1 values ("2021-01-24 19:22:10", 2014, "YYYY-MQ-DD");
+create table t2 (a varchar(30)) engine=myisam;
+insert into t2 select to_char(a,f) from t1;
+Warnings:
+Warning	4175	Oracle compatibility function error: date format not recognized at MQ-DD
+set @@sql_mode="STRICT_ALL_TABLES";
+insert into t2 select to_char(a,f) from t1;
+ERROR HY000: Oracle compatibility function error: date format not recognized at MQ-DD
+select * from t2;
+a
+2021-01-24
+NULL
+2021-01-24
+drop table t1,t2;
+set @local.sql_mode=@sql_mode;
diff --git a/mysql-test/suite/compat/oracle/t/func_to_char.test b/mysql-test/suite/compat/oracle/t/func_to_char.test
new file mode 100644
index 00000000000..8956244dadc
--- /dev/null
+++ b/mysql-test/suite/compat/oracle/t/func_to_char.test
@@ -0,0 +1,226 @@
+##############################################################
+# testcase for TO_CHAR() function for oracle
+# Part of MDEV-20017 Implement TO_CHAR() Oracle compatible function
+##############################################################
+
+# Save sql_mode
+set @save_sql_mode=@@sql_mode;
+
+--echo #
+--echo # test for datetime
+--echo #
+
+CREATE TABLE t_to_char1(c0 int, c1 date, c2 time, c3 datetime);
+
+INSERT INTO t_to_char1 VALUES (1, '1000-1-1', '00:00:00', '1000-1-1 00:00:00');
+INSERT INTO t_to_char1 VALUES (2, '9999-12-31', '23:59:59', '9999-12-31 23:59:59');
+INSERT INTO t_to_char1 VALUES (3, '2021-01-03', '08:30:00', '2021-01-03 08:30:00');
+INSERT INTO t_to_char1 VALUES (4, '2021-07-03', '18:30:00', '2021-07-03 18:30:00');
+
+CREATE TABLE t_to_char2(c1 timestamp);
+INSERT INTO t_to_char2 VALUES ('1980-01-11 04:50:39');
+INSERT INTO t_to_char2 VALUES ('2000-11-11 12:50:00');
+INSERT INTO t_to_char2 VALUES ('2030-11-11 18:20:10');
+
+# test for timestamp
+SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char2;
+SELECT TO_CHAR(c1, 'HH24-MI-SS') FROM t_to_char2;
+
+# test full output format
+--echo #
+--echo # test YYYY/YY/MM/DD/HH/HH24/MI/SS
+--echo #
+SELECT TO_CHAR(c1, 'YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'yyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'yy-mm-dd hh24:mi:ss') AS C3 FROM t_to_char1;
+
+--echo #
+--echo # test YYY/Y/MON/DD/DY/HH/HH12/MI/SS
+--echo #
+SELECT TO_CHAR(c1, 'YYY-MON-DD') AS C1, TO_CHAR(c2, 'HH12:MI:SS') AS C2, TO_CHAR(c3, 'Y-MONTH-DY HH:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'yyy-Mon-Dd') AS C1, TO_CHAR(c2, 'Hh12:mi:Ss') AS C2, TO_CHAR(c3, 'y-Month-Dy Hh:Mi:Ss') AS C3 FROM t_to_char1;
+
+--echo #
+--echo # test RRRR/RR/DAY
+--echo #
+SELECT TO_CHAR(c1, 'RRRR-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'RRRR-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'RR-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'YY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'Rrrr-Mm-Dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'Rrrr-mm-dd Hh24:mi:ss') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'rr-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'yy-mm-dd hh24:Mi:ss') AS C3 FROM t_to_char1;
+
+--echo #
+--echo # test AD/A.D./BC/B.C./AM/A.M./PM/P.M.
+--echo #
+SELECT TO_CHAR(c1, 'ADYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'AD.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'ADYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'AD.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'BCYYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'BCYYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'B.C.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'B.C.YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'bcyyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mi:ss') AS C2, TO_CHAR(c3, 'BcYYyy-MM-DD Hh24:mi:sS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'b.c.yyyy-mm-dd') AS C1, TO_CHAR(c2, 'hh:mI:Ss') AS C2, TO_CHAR(c3, 'b.C.Yyyy-Mm-dd hH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'PMHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD P.M.HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'pmHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD p.m.HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'AMHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD A.m.HH24:MI:SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'A.D.YYYY-MM-DD') AS C1, TO_CHAR(c2, 'amHH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD a.M.HH24:MI:SS') AS C3 FROM t_to_char1;
+
+--echo #
+--echo # test format without order
+--echo #
+SELECT TO_CHAR(c1, 'MM-YYYY-DD') AS C1, TO_CHAR(c2, 'HH:SS:MI') AS C2, TO_CHAR(c3, 'DD-YY-MM MI:SS:HH24') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'yyy-Dd-Mon') AS C1, TO_CHAR(c2, 'mi:Hh12:Ss') AS C2, TO_CHAR(c3, 'Ss:Hh:Mi Dy-y-Month') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'Dd-Mm-Rrrr') AS C1, TO_CHAR(c2, 'ss:hh:mi') AS C2, TO_CHAR(c3, 'ss:Rrrr-hh24-dd mon:mi') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'YYYYA.D.-MM-DD') AS C1, TO_CHAR(c2, 'HH:MI:SS') AS C2, TO_CHAR(c3, 'A.D..YYYY-MM-DD HH24:MI:SS') AS C3 FROM t_to_char1;
+
+--echo #
+--echo # test for special characters
+--echo #
+SELECT TO_CHAR(c1, 'YYYYMMDD') AS C1, TO_CHAR(c2, 'HHMISS') AS C2, TO_CHAR(c3, 'YYMMDDHH24MISS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'YYYY!!MM@DD') AS C1, TO_CHAR(c2, 'HH#MI$SS') AS C2, TO_CHAR(c3, 'YY%MM^DD*HH24(MI)SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'YYYY_MM+DD') AS C1, TO_CHAR(c2, 'HH=MI{SS') AS C2, TO_CHAR(c3, 'YY}MMDDHH24MISS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'YYYY,MM.DD') AS C1, TO_CHAR(c2, 'HH/MI;SS') AS C2, TO_CHAR(c3, 'YY>MM<DD]HH24[MI\SS') AS C3 FROM t_to_char1;
+SELECT TO_CHAR(c1, 'YYYY||||MM|DD') AS C1, TO_CHAR(c2, 'HH&|MI|&|SS') AS C2, TO_CHAR(c3, 'YY&&&\\MM|&&|DD HH24|| MI&||"abx"|SS') AS C3 FROM t_to_char1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+SELECT TO_CHAR(c1, 'YYYY&MM-DD') FROM t_to_char1 where c0=1;
+SELECT TO_CHAR(c1, 'YYYY"abx"MM"bsz"DD') AS C1 FROM t_to_char1;
+
+--echo #
+--echo # test for other locale
+--echo #
+SET character_set_client='utf8';
+SET character_set_connection='utf8';
+SET character_set_results='utf8';
+SET lc_time_names='zh_TW';
+SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
+SET lc_time_names='de_DE';
+SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
+SET lc_time_names='en_US';
+SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
+SET lc_time_names='zh_CN';
+SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
+
+--echo #
+--echo # test for invalid format
+--echo #
+
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+SELECT TO_CHAR(c1, 'YYYYaxMON-DAY') FROM t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+SELECT TO_CHAR(c1, 'YYYY\nMON-DAY') FROM t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+SELECT TO_CHAR(c1, 'YYYY\rMON-DAY') FROM t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+SELECT TO_CHAR(c1, 'YYYY分隔MON-DAY') FROM t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+SELECT TO_CHAR(c1, 'YYYY-分隔MON-DAY') FROM t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(c3, 'YYYYxDDD') from t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(c3, 'YYYY&DDD') from t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(c3, 'xxYYYY-DD') from t_to_char1 where c0 = 1;
+
+SET character_set_client='latin1';
+SET character_set_connection='latin1';
+SET character_set_results='latin1';
+--echo #
+--echo # test for unusual format
+--echo #
+select to_char(c3, 'YYYYYYYYYYYYYYY') from t_to_char1;
+select to_char(c3, 'YYYYYYYYYYYYYYYDDDDDD') from t_to_char1;
+
+--echo #
+--echo # oracle max length is 144
+--echo #
+
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY') from t_to_char1 where c0 = 1;
+CREATE TABLE t_f(c1 varchar(150));
+insert into t_f values('YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD');
+select to_char('2000-11-11', c1) from t_f;
+DROP TABLE t_f;
+select to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD-MM') from t_to_char1 where c0 = 1;
+
+--echo #
+--echo # now only support two parameter.
+--echo #
+select to_char(c3) from t_to_char1 where c0 =1;
+select to_char(c3, "YYYY-MM-DD HH:MI:SS") from t_to_char1 where c0 =1;
+--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
+select to_char(c3, "YYYY-MM-DD HH:MI:SS", "zh_CN") from t_to_char1 where c0 = 1;
+--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
+select to_char(c3, "YYYY-MM-DD HH:MI:SS", "NLS_DATE_LANGUAGE = zh_CN") from t_to_char1 where c0 = 1;
+
+--echo #
+--echo # oracle support format but mariadb does not support
+--echo #
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(c3, 'DDD') from t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(c3, 'D') from t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(c3, 'DS') from t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(c3, 'IY') from t_to_char1 where c0 = 1;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(c3, 'IYYY') from t_to_char1 where c0 = 1;
+
+--echo #
+--echo # test for first argument data type
+--echo # 
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(1, 'yyyy');
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+select to_char(1.1, 'yyyy');
+CREATE TABLE t_a(c1 int, c2 float, c3 decimal, c4 char(20), c5 varchar(20), c6 nchar(20), c7 nvarchar(20));
+insert into t_a VALUES (1, 3.2, 2002.02, '2000-11-11', '2000-11-11', '2000-11-11', '2000-11-11');
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+SELECT TO_CHAR(c1, 'YYYY') from t_a;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+SELECT TO_CHAR(c2, 'YYYY') from t_a;
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+SELECT TO_CHAR(c3, 'YYYY') from t_a;
+SELECT TO_CHAR(c4, 'YYYY') from t_a;
+SELECT TO_CHAR(c5, 'YYYY') from t_a;
+SELECT TO_CHAR(c6, 'YYYY') from t_a;
+SELECT TO_CHAR(c7, 'YYYY') from t_a;
+DROP TABLE t_a;
+
+CREATE TABLE t_b(c0 int, c1 char(20), c2 varchar(20), c3 nchar(20), c4 nvarchar(20));
+INSERT INTO t_b VALUES (1111, 'YYYY-MM-DD', 'YYYY-MM-DD', 'YYYY-MM-DD', 'YYYY-MM-DD');
+SELECT TO_CHAR('2000-11-11', c0) FROM t_b;
+SELECT TO_CHAR('2000-11-11', c1) FROM t_b;
+SELECT TO_CHAR('2000-11-11', c2) FROM t_b;
+SELECT TO_CHAR('2000-11-11', c3) FROM t_b;
+SELECT TO_CHAR('2000-11-11', c4) FROM t_b;
+DROP TABLE t_b;
+
+EXPLAIN EXTENDED SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char1;
+
+--echo #
+--echo # test for time type with date format string
+--echo #
+SELECT TO_CHAR(c2, 'YYYY-MM-DD HH:MI:SS') from t_to_char1;
+SELECT TO_CHAR(c2, 'YYYY-MON-DY HH:MI:SS') from t_to_char1;
+SELECT TO_CHAR(c2, 'MON-YYYY-DY HH:MI:SS') from t_to_char1;
+SELECT TO_CHAR(c2, 'YYYY-MONTH-DAY HH:MI:SS') from t_to_char1;
+
+DROP TABLE t_to_char1;
+DROP TABLE t_to_char2;
+
+
+--echo #
+--echo # Test strict mode
+--echo #
+
+create table t1 (a datetime, b int, f varchar(30)) engine=myisam;
+insert into t1 values ("2021-01-24 19:22:10", 2014, "YYYY-MM-DD");
+insert into t1 values ("2021-01-24 19:22:10", 2014, "YYYY-MQ-DD");
+create table t2 (a varchar(30)) engine=myisam;
+insert into t2 select to_char(a,f) from t1;
+set @@sql_mode="STRICT_ALL_TABLES";
+--error ER_ORACLE_COMPAT_FUNCTION_ERROR
+insert into t2 select to_char(a,f) from t1;
+select * from t2;
+drop table t1,t2;
+set @local.sql_mode=@sql_mode;
+
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 6c0156aa17b..25f479a3eb3 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -2132,6 +2132,19 @@ protected:
 };
 
 
+class Create_func_to_char : public Create_native_func
+{
+public:
+  virtual Item *create_native(THD *thd, LEX_CSTRING *name, List<Item> *item_list);
+
+  static Create_func_to_char s_singleton;
+
+protected:
+  Create_func_to_char() {}
+  virtual ~Create_func_to_char() {}
+};
+
+
 class Create_func_to_days : public Create_func_arg1
 {
 public:
@@ -5142,6 +5155,44 @@ Create_func_to_base64::create_1_arg(THD *thd, Item *arg1)
 }
 
 
+Create_func_to_char Create_func_to_char::s_singleton;
+
+Item*
+Create_func_to_char::create_native(THD *thd, LEX_CSTRING *name,
+				   List<Item> *item_list)
+{
+  Item *func= NULL;
+  int arg_count= 0;
+
+  if (item_list != NULL)
+    arg_count= item_list->elements;
+
+  switch (arg_count) {
+  case 1:
+  {
+    Item *param_1= item_list->pop();
+    Item *i0= new (thd->mem_root) Item_string_sys(thd, "YYYY-MM-DD HH24:MI:SS",  21);
+    func= new (thd->mem_root) Item_func_tochar(thd, param_1, i0);
+    break;
+  }
+  case 2:
+  {
+    Item *param_1= item_list->pop();
+    Item *param_2= item_list->pop();
+    func= new (thd->mem_root) Item_func_tochar(thd, param_1, param_2);
+    break;
+  }
+  default:
+  {
+    my_error(ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT, MYF(0), name->str);
+    break;
+  }
+  }
+
+  return func;
+}
+
+
 Create_func_to_days Create_func_to_days::s_singleton;
 
 Item*
@@ -5601,6 +5652,7 @@ static Native_func_registry func_array[] =
   { { STRING_WITH_LEN("TIME_FORMAT") }, BUILDER(Create_func_time_format)},
   { { STRING_WITH_LEN("TIME_TO_SEC") }, BUILDER(Create_func_time_to_sec)},
   { { STRING_WITH_LEN("TO_BASE64") }, BUILDER(Create_func_to_base64)},
+  { { STRING_WITH_LEN("TO_CHAR") }, BUILDER(Create_func_to_char)},
   { { STRING_WITH_LEN("TO_DAYS") }, BUILDER(Create_func_to_days)},
   { { STRING_WITH_LEN("TO_SECONDS") }, BUILDER(Create_func_to_seconds)},
   { { STRING_WITH_LEN("UCASE") }, BUILDER(Create_func_ucase)},
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 04d913b0fca..44d2ec7912d 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -59,7 +59,6 @@
 /** Day number for Dec 31st, 9999. */
 #define MAX_DAY_NUMBER 3652424L
 
-
 Func_handler_date_add_interval_datetime_arg0_time
   func_handler_date_add_interval_datetime_arg0_time;
 
@@ -1914,6 +1913,805 @@ null_date:
   return 0;
 }
 
+/*
+  Oracle has many formatting models, we list all but only part of them
+  are implemented, because some models depend on oracle functions
+  which mariadb is not supported.
+
+  Models for datetime, used by TO_CHAR/TO_DATE. Normal format characters are
+  stored as short integer < 256, while format characters are stored as a
+  integer > 256
+*/
+
+#define FMT_BASE       128
+#define FMT_AD         FMT_BASE+1
+#define FMT_AD_DOT     FMT_BASE+2
+#define FMT_AM         FMT_BASE+3
+#define FMT_AM_DOT     FMT_BASE+4
+#define FMT_BC         FMT_BASE+5
+#define FMT_BC_DOT     FMT_BASE+6
+#define FMT_CC         FMT_BASE+7
+#define FMT_SCC        FMT_BASE+8
+#define FMT_D          FMT_BASE+9
+#define FMT_DAY        FMT_BASE+10
+#define FMT_DD         FMT_BASE+11
+#define FMT_DDD        FMT_BASE+12
+#define FMT_DL         FMT_BASE+13
+#define FMT_DS         FMT_BASE+14
+#define FMT_DY         FMT_BASE+15
+#define FMT_E          FMT_BASE+16
+#define FMT_EE         FMT_BASE+17
+#define FMT_FF         FMT_BASE+18
+#define FMT_FM         FMT_BASE+19
+#define FMT_FX         FMT_BASE+20
+#define FMT_HH         FMT_BASE+21
+#define FMT_HH12       FMT_BASE+22
+#define FMT_HH24       FMT_BASE+23
+#define FMT_IW         FMT_BASE+24
+#define FMT_I          FMT_BASE+25
+#define FMT_IY         FMT_BASE+26
+#define FMT_IYY        FMT_BASE+27
+#define FMT_IYYY       FMT_BASE+28
+#define FMT_J          FMT_BASE+29
+#define FMT_MI         FMT_BASE+30
+#define FMT_MM         FMT_BASE+31
+#define FMT_MON        FMT_BASE+32
+#define FMT_MONTH      FMT_BASE+33
+#define FMT_PM         FMT_BASE+34
+#define FMT_PM_DOT     FMT_BASE+35
+#define FMT_RM         FMT_BASE+37
+#define FMT_RR         FMT_BASE+38
+#define FMT_RRRR       FMT_BASE+39
+#define FMT_SS         FMT_BASE+40
+#define FMT_SSSSSS     FMT_BASE+41
+#define FMT_TS         FMT_BASE+42
+#define FMT_TZD        FMT_BASE+43
+#define FMT_TZH        FMT_BASE+44
+#define FMT_TZM        FMT_BASE+45
+#define FMT_TZR        FMT_BASE+46
+#define FMT_W          FMT_BASE+47
+#define FMT_WW         FMT_BASE+48
+#define FMT_X          FMT_BASE+49
+#define FMT_Y          FMT_BASE+50
+#define FMT_YY         FMT_BASE+51
+#define FMT_YYY        FMT_BASE+52
+#define FMT_YYYY       FMT_BASE+53
+#define FMT_YYYY_COMMA FMT_BASE+54
+#define FMT_YEAR       FMT_BASE+55
+#define FMT_SYYYY      FMT_BASE+56
+#define FMT_SYEAR      FMT_BASE+57
+
+
+/**
+  Modify the quotation flag and check whether the subsequent process is skipped
+
+  @param cftm             Character or FMT... format descriptor
+  @param quotation_flag   Points to 'true' if we are inside a quoted string
+
+  @return true  If we are inside a quoted string or if we found a '"' character
+  @return false Otherwise
+*/
+
+static inline bool check_quotation(uint16 cfmt, bool *quotation_flag)
+{
+  if (cfmt == '"')
+  {
+    *quotation_flag= !*quotation_flag;
+    return true;
+  }
+  return *quotation_flag;
+}
+
+#define INVALID_CHARACTER(x) (((x) >= 'A' && (x) <= 'Z') ||((x) >= '0' && (x) <= '9') || (x) >= 127 || ((x) < 32))
+
+
+/**
+  Special characters are directly output in the result
+
+  @return 0  If found not acceptable character
+  @return #  Number of copied characters
+*/
+
+static uint parse_special(char cfmt, const char *ptr, const char *end,
+                         uint16 *array)
+{
+  int offset= 0;
+  char tmp1;
+
+  /* Non-printable character and Multibyte encoded characters */
+  if (INVALID_CHARACTER(cfmt))
+    return 0;
+
+  /*
+   * '&' with text is used for variable input, but '&' with other
+   * special charaters like '|'. '*' is used as separator
+   */
+  if (cfmt == '&' && ptr + 1 < end)
+  {
+    tmp1= my_toupper(system_charset_info, *(ptr+1));
+    if (tmp1 >= 'A' && tmp1 <= 'Z')
+      return 0;
+  }
+
+  do {
+    /*
+      Continuously store the special characters in fmt_array until non-special
+      characters appear
+     */
+    *array++= (uint16) (uchar) *ptr++;
+    offset++;
+    if (ptr == end)
+      break;
+    tmp1= my_toupper(system_charset_info, *ptr);
+  } while (!INVALID_CHARACTER(tmp1) && tmp1 != '"');
+  return offset;
+}
+
+
+/**
+  Parse the format string, convert it to an compact array and calculate the
+  length of output string
+
+  @param format   Format string
+  @param fmt_len  Function will store max length of formated date string here
+
+  @return 0 ok. fmt_len is updated
+  @return 1 error.  In this case 'warning_string' is set to error message
+*/
+
+bool Item_func_tochar::parse_format_string(const String *format, uint *fmt_len)
+{
+  const char *ptr, *end;
+  uint16 *tmp_fmt= fmt_array;
+  uint tmp_len= 0;
+  int offset= 0;
+  bool quotation_flag= false;
+
+  ptr= format->ptr();
+  end= ptr + format->length();
+
+  if (format->length() > MAX_DATETIME_FORMAT_MODEL_LEN)
+  {
+    warning_message.append(STRING_WITH_LEN("datetime format string is too "
+                                           "long"));
+    return 1;
+  }
+
+  for (; ptr < end; ptr++, tmp_fmt++)
+  {
+    uint ulen;
+    char cfmt, next_char;
+
+    cfmt= my_toupper(system_charset_info, *ptr);
+
+    /*
+      Oracle datetime format support text in double quotation marks like
+      'YYYY"abc"MM"xyz"DD', When this happens, store the text and quotation
+      marks, and use the text as a separator in make_date_time_oracle.
+
+      NOTE: the quotation mark is not print in return value. for example:
+      select TO_CHAR(sysdate, 'YYYY"abc"MM"xyzDD"') will return 2021abc01xyz11
+     */
+    if (check_quotation(cfmt, &quotation_flag))
+    {
+      *tmp_fmt= *ptr;
+      tmp_len+= 1;
+      continue;
+    }
+
+    switch (cfmt) {
+    case 'A':                                   // AD/A.D./AM/A.M.
+      if (ptr+1 >= end)
+        goto error;
+      next_char= my_toupper(system_charset_info, *(ptr+1));
+      if (next_char == 'D')
+      {
+        *tmp_fmt= FMT_AD;
+        ptr+= 1;
+        tmp_len+= 2;
+      }
+      else if (next_char == 'M')
+      {
+        *tmp_fmt= FMT_AM;
+        ptr+= 1;
+        tmp_len+= 2;
+      }
+      else if (next_char == '.' && ptr+3 < end && *(ptr+3) == '.')
+      {
+        if (my_toupper(system_charset_info, *(ptr+2)) == 'D')
+        {
+          *tmp_fmt= FMT_AD_DOT;
+          ptr+= 3;
+          tmp_len+= 4;
+        }
+        else if (my_toupper(system_charset_info, *(ptr+2)) == 'M')
+        {
+          *tmp_fmt= FMT_AM_DOT;
+          ptr+= 3;
+          tmp_len+= 4;
+        }
+        else
+          goto error;
+      }
+      else
+        goto error;
+      break;
+    case 'B':                                     // BC and B.C
+      if (ptr+1 >= end)
+        goto error;
+      next_char= my_toupper(system_charset_info, *(ptr+1));
+      if (next_char == 'C')
+      {
+        *tmp_fmt= FMT_BC;
+        ptr+= 1;
+        tmp_len+= 2;
+      }
+      else if (next_char == '.' && ptr+3 < end &&
+               my_toupper(system_charset_info, *(ptr+2)) == 'C' &&
+               *(ptr+3) == '.')
+      {
+        *tmp_fmt= FMT_BC_DOT;
+        ptr+= 3;
+        tmp_len+= 4;
+      }
+      else
+        goto error;
+      break;
+    case 'P':                                   // PM or P.M.
+      next_char= my_toupper(system_charset_info, *(ptr+1));
+      if (next_char == 'M')
+      {
+        *tmp_fmt= FMT_PM;
+        ptr+= 1;
+        tmp_len+= 2;
+      }
+      else if (next_char == '.' &&
+               my_toupper(system_charset_info, *(ptr+2)) == 'M' &&
+               my_toupper(system_charset_info, *(ptr+3)) == '.')
+      {
+        *tmp_fmt= FMT_PM_DOT;
+        ptr+= 3;
+        tmp_len+= 4;
+      }
+      else
+        goto error;
+      break;
+    case 'Y':                                   // Y, YY, YYY o YYYYY
+      if (ptr + 1 == end || my_toupper(system_charset_info, *(ptr+1)) != 'Y')
+      {
+        *tmp_fmt= FMT_Y;
+        tmp_len+= 1;
+        break;
+      }
+      if (ptr + 2 == end ||
+          my_toupper(system_charset_info, *(ptr+2)) != 'Y') /* YY */
+      {
+        *tmp_fmt= FMT_YY;
+        ulen= 2;
+      }
+      else
+      {
+        if (ptr + 3 < end && my_toupper(system_charset_info, *(ptr+3)) == 'Y')
+        {
+          *tmp_fmt= FMT_YYYY;
+          ulen= 4;
+        }
+        else
+        {
+          *tmp_fmt= FMT_YYY;
+          ulen= 3;
+        }
+      }
+      ptr+= ulen-1;
+      tmp_len+= ulen;
+      break;
+
+    case 'R':                                   // RR or RRRR
+      if (ptr + 1 == end || my_toupper(system_charset_info, *(ptr+1)) != 'R')
+        goto error;
+
+      if (ptr + 2 == end || my_toupper(system_charset_info, *(ptr+2)) != 'R')
+      {
+        *tmp_fmt= FMT_RR;
+        ulen= 2;
+      }
+      else
+      {
+        if (ptr + 3 >= end || my_toupper(system_charset_info, *(ptr+3)) != 'R')
+          goto error;
+        *tmp_fmt= FMT_RRRR;
+        ulen= 4;
+      }
+      ptr+= ulen-1;
+      tmp_len+= ulen;
+      break;
+    case 'M':
+    {
+      char tmp1;
+      if (ptr + 1 >= end)
+        goto error;
+
+      tmp1= my_toupper(system_charset_info, *(ptr+1));
+      if (tmp1 == 'M')
+      {
+        *tmp_fmt= FMT_MM;
+        tmp_len+= 2;
+        ptr+= 1;
+      }
+      else if (tmp1 == 'I')
+      {
+        *tmp_fmt= FMT_MI;
+        tmp_len+= 2;
+        ptr+= 1;
+      }
+      else if (tmp1 == 'O')
+      {
+        if (ptr + 2 >= end)
+          goto error;
+        char tmp2= my_toupper(system_charset_info, *(ptr+2));
+        if (tmp2 != 'N')
+          goto error;
+
+        if (ptr + 4 >= end ||
+            my_toupper(system_charset_info, *(ptr+3)) != 'T' ||
+            my_toupper(system_charset_info, *(ptr+4)) != 'H')
+        {
+          *tmp_fmt= FMT_MON;
+          tmp_len+= 3;
+          ptr+= 2;
+        }
+        else
+        {
+          *tmp_fmt= FMT_MONTH;
+          tmp_len+= (locale->max_month_name_length *
+                     my_charset_utf8mb3_bin.mbmaxlen);
+          ptr+= 4;
+        }
+      }
+      else
+        goto error;
+    }
+    break;
+    case 'D':                                   // DD, DY, or DAY
+    {
+      if (ptr + 1 >= end)
+        goto error;
+      char tmp1= my_toupper(system_charset_info, *(ptr+1));
+
+      if (tmp1 == 'D')
+      {
+        *tmp_fmt= FMT_DD;
+        tmp_len+= 2;
+      }
+      else if (tmp1 == 'Y')
+      {
+        *tmp_fmt= FMT_DY;
+        tmp_len+= 3;
+      }
+      else if (tmp1 == 'A')                     // DAY
+      {
+        if (ptr + 2 == end || my_toupper(system_charset_info, *(ptr+2)) != 'Y')
+          goto error;
+        *tmp_fmt= FMT_DAY;
+        tmp_len+= locale->max_day_name_length * my_charset_utf8mb3_bin.mbmaxlen;
+        ptr+= 1;
+      }
+      else
+        goto error;
+      ptr+= 1;
+    }
+    break;
+    case 'H':                                   // HH, HH12 or HH23
+    {
+      char tmp1, tmp2, tmp3;
+      if (ptr + 1 >= end)
+        goto error;
+      tmp1= my_toupper(system_charset_info, *(ptr+1));
+
+      if (tmp1 != 'H')
+        goto error;
+
+      if (ptr+3 >= end)
+      {
+        *tmp_fmt= FMT_HH;
+        ptr+= 1;
+      }
+      else
+      {
+        tmp2= *(ptr+2);
+        tmp3= *(ptr+3);
+
+        if (tmp2 == '1' && tmp3 == '2')
+        {
+          *tmp_fmt= FMT_HH12;
+          ptr+= 3;
+        }
+        else if (tmp2 == '2' && tmp3 == '4')
+        {
+          *tmp_fmt= FMT_HH24;
+          ptr+= 3;
+        }
+        else
+        {
+          *tmp_fmt= FMT_HH;
+          ptr+= 1;
+        }
+      }
+      tmp_len+= 2;
+      break;
+    }
+    case 'S':                                   // SS
+      if (ptr + 1 == end || my_toupper(system_charset_info, *(ptr+1)) != 'S')
+        goto error;
+
+      *tmp_fmt= FMT_SS;
+      tmp_len+= 2;
+      ptr+= 1;
+      break;
+    case '|':
+      /*
+        If only one '|' just ignore it, else append others, for example:
+        TO_CHAR('2000-11-05', 'YYYY|MM||||DD') --> 200011|||05
+      */
+      if (ptr + 1 == end || *(ptr+1) != '|')
+      {
+        tmp_fmt--;
+        break;
+      }
+      ptr++;                                    // Skip first '|'
+      do
+      {
+        *tmp_fmt++= *ptr++;
+        tmp_len++;
+      } while ((ptr < end) && *ptr == '|');
+      ptr--;                                    // Fix ptr for above for loop
+      tmp_fmt--;
+      break;
+
+    default:
+      offset= parse_special(cfmt, ptr, end, tmp_fmt);
+      if (!offset)
+        goto error;
+      /* ptr++ is in the for loop, so we must move ptr to offset-1 */
+      ptr+= (offset-1);
+      tmp_fmt+= (offset-1);
+      tmp_len+= offset;
+      break;
+    }
+  }
+  *fmt_len= tmp_len;
+  *tmp_fmt= 0;
+  return 0;
+
+error:
+  warning_message.append(STRING_WITH_LEN("date format not recognized at "));
+  warning_message.append(ptr, MY_MIN(8, end- ptr));
+  return 1;
+}
+
+
+static inline bool append_val(int val, int size, String *str)
+{
+  ulong len= 0;
+  char intbuff[15];
+
+  len= (ulong) (int10_to_str(val, intbuff, 10) - intbuff);
+  return str->append_with_prefill(intbuff, len, size, '0');
+}
+
+
+static bool make_date_time_oracle(const uint16 *fmt_array,
+                                  const MYSQL_TIME *l_time,
+                                  const MY_LOCALE *locale,
+                                  String *str)
+{
+  bool quotation_flag= false;
+  const uint16 *ptr= fmt_array;
+  uint hours_i;
+  uint weekday;
+
+  str->length(0);
+
+  while (*ptr)
+  {
+    if (check_quotation(*ptr, &quotation_flag))
+    {
+      /* don't display '"' in the result, so if it is '"', skip it */
+      if (*ptr != '"')
+      {
+        DBUG_ASSERT(*ptr <= 255);
+        str->append((char) *ptr);
+      }
+      ptr++;
+      continue;
+    }
+
+    switch (*ptr) {
+
+    case FMT_AM:
+    case FMT_PM:
+      if (l_time->hour > 11)
+        str->append("PM", 2);
+      else
+        str->append("AM", 2);
+      break;
+
+    case FMT_AM_DOT:
+    case FMT_PM_DOT:
+      if (l_time->hour > 11)
+        str->append(STRING_WITH_LEN("P.M."));
+      else
+        str->append(STRING_WITH_LEN("A.M."));
+      break;
+
+    case FMT_AD:
+    case FMT_BC:
+      if (l_time->year > 0)
+        str->append(STRING_WITH_LEN("AD"));
+      else
+        str->append(STRING_WITH_LEN("BC"));
+      break;
+
+    case FMT_AD_DOT:
+    case FMT_BC_DOT:
+      if (l_time->year > 0)
+        str->append(STRING_WITH_LEN("A.D."));
+      else
+        str->append(STRING_WITH_LEN("B.C."));
+      break;
+
+    case FMT_Y:
+      if (append_val(l_time->year%10, 1, str))
+        goto err_exit;
+      break;
+
+    case FMT_YY:
+    case FMT_RR:
+      if (append_val(l_time->year%100, 2, str))
+        goto err_exit;
+      break;
+
+    case FMT_YYY:
+      if (append_val(l_time->year%1000, 3, str))
+        goto err_exit;
+      break;
+
+    case FMT_YYYY:
+    case FMT_RRRR:
+      if (append_val(l_time->year, 4, str))
+        goto err_exit;
+      break;
+
+    case FMT_MM:
+      if (append_val(l_time->month, 2, str))
+        goto err_exit;
+      break;
+
+    case FMT_MON:
+      {
+        if (l_time->month == 0)
+        {
+          str->append("00", 2);
+        }
+        else
+        {
+          const char *month_name= (locale->ab_month_names->
+                                   type_names[l_time->month-1]);
+          size_t m_len= strlen(month_name);
+          str->append(month_name, m_len, system_charset_info);
+        }
+      }
+      break;
+
+    case FMT_MONTH:
+      {
+        if (l_time->month == 0)
+        {
+          str->append("00", 2);
+        }
+        else
+        {
+          const char *month_name= (locale->month_names->
+                                   type_names[l_time->month-1]);
+          size_t month_byte_len= strlen(month_name);
+          size_t month_char_len;
+          str->append(month_name, month_byte_len, system_charset_info);
+          month_char_len= my_numchars_mb(&my_charset_utf8mb3_general_ci,
+                                         month_name, month_name +
+                                         month_byte_len);
+          if (str->strfill(' ', locale->max_month_name_length - month_char_len))
+            goto err_exit;
+        }
+      }
+      break;
+
+    case FMT_DD:
+      if (append_val(l_time->day, 2, str))
+        goto err_exit;
+      break;
+
+    case FMT_DY:
+      {
+        if (l_time->day == 0)
+          str->append("00", 2);
+        else
+        {
+          weekday= calc_weekday(calc_daynr(l_time->year,l_time->month,
+                                          l_time->day), 0);
+          const char *day_name= locale->ab_day_names->type_names[weekday];
+          str->append(day_name, strlen(day_name), system_charset_info);
+        }
+      }
+      break;
+
+    case FMT_DAY:
+      {
+        if (l_time->day == 0)
+          str->append("00", 2, system_charset_info);
+        else
+        {
+          const char *day_name;
+          size_t day_byte_len, day_char_len;
+          weekday=calc_weekday(calc_daynr(l_time->year,l_time->month,
+                                          l_time->day), 0);
+          day_name= locale->day_names->type_names[weekday];
+          day_byte_len= strlen(day_name);
+          str->append(day_name, day_byte_len, system_charset_info);
+          day_char_len= my_numchars_mb(&my_charset_utf8mb3_general_ci,
+                                       day_name, day_name + day_byte_len);
+          if (str->strfill(' ', locale->max_day_name_length - day_char_len))
+            goto err_exit;
+        }
+      }
+      break;
+
+    case FMT_HH12:
+    case FMT_HH:
+      hours_i= (l_time->hour%24 + 11)%12+1;
+      if (append_val(hours_i, 2, str))
+        goto err_exit;
+      break;
+
+    case FMT_HH24:
+      if (append_val(l_time->hour, 2, str))
+        goto err_exit;
+      break;
+
+    case FMT_MI:
+      if (append_val(l_time->minute, 2, str))
+        goto err_exit;
+      break;
+
+    case FMT_SS:
+      if (append_val(l_time->second, 2, str))
+        goto err_exit;
+      break;
+
+    default:
+      str->append((char) *ptr);
+    }
+
+    ptr++;
+  };
+  return false;
+
+err_exit:
+  return true;
+}
+
+
+bool Item_func_tochar::fix_length_and_dec()
+{
+  thd= current_thd;
+  CHARSET_INFO *cs= thd->variables.collation_connection;
+  Item *arg1= args[1]->this_item();
+  my_repertoire_t repertoire= arg1->collation.repertoire;
+  StringBuffer<STRING_BUFFER_USUAL_SIZE> buffer;
+  String *str;
+
+  locale= thd->variables.lc_time_names;
+  if (!thd->variables.lc_time_names->is_ascii)
+    repertoire|= MY_REPERTOIRE_EXTENDED;
+  collation.set(cs, arg1->collation.derivation, repertoire);
+
+  /* first argument must be datetime or string */
+  enum_field_types arg0_mysql_type= args[0]->field_type();
+
+  max_length= 0;
+  switch (arg0_mysql_type) {
+  case MYSQL_TYPE_TIME:
+  case MYSQL_TYPE_DATE:
+  case MYSQL_TYPE_DATETIME:
+  case MYSQL_TYPE_TIMESTAMP:
+  case MYSQL_TYPE_VARCHAR:
+  case MYSQL_TYPE_STRING:
+    break;
+  default:
+  {
+    my_printf_error(ER_ORACLE_COMPAT_FUNCTION_ERROR,
+                    ER(ER_ORACLE_COMPAT_FUNCTION_ERROR),
+                    MYF(0),
+                    "data type of first argument must be type "
+                    "date/datetime/time or string");
+    return TRUE;
+  }
+  }
+  if (args[1]->basic_const_item() && (str= args[1]->val_str(&buffer)))
+  {
+    uint ulen;
+    fixed_length= 1;
+    if (parse_format_string(str, &ulen))
+    {
+      my_printf_error(ER_ORACLE_COMPAT_FUNCTION_ERROR,
+                      ER(ER_ORACLE_COMPAT_FUNCTION_ERROR),
+                      MYF(0),
+                      warning_message.c_ptr());
+      return TRUE;
+    }
+    max_length= (uint32) (ulen * collation.collation->mbmaxlen);
+  }
+  else
+  {
+    fixed_length= 0;
+    max_length= (uint32) MY_MIN(arg1->max_length * 10 *
+                                collation.collation->mbmaxlen,
+                                MAX_BLOB_WIDTH);
+  }
+  set_maybe_null();
+  return FALSE;
+}
+
+
+String *Item_func_tochar::val_str(String* str)
+ {
+  StringBuffer<64> format_buffer;
+  String *format;
+  MYSQL_TIME l_time;
+  const MY_LOCALE *lc= locale;
+  date_conv_mode_t mode= TIME_CONV_NONE;
+  size_t max_result_length= max_length;
+
+  if (warning_message.length())
+    goto null_date;
+
+  if ((null_value= args[0]->get_date(thd, &l_time,
+                                     Temporal::Options(mode, thd))))
+    return 0;
+
+  if (!fixed_length)
+  {
+    uint ulen;
+    if (!(format= args[1]->val_str(&format_buffer)) || !format->length() ||
+        parse_format_string(format, &ulen))
+      goto null_date;
+    max_result_length= ((size_t) ulen) * collation.collation->mbmaxlen;
+  }
+
+  if (str->alloc(max_result_length))
+    goto null_date;
+
+  /* Create the result string */
+  str->set_charset(collation.collation);
+  if (!make_date_time_oracle(fmt_array, &l_time, lc, str))
+    return str;
+
+null_date:
+
+  if (warning_message.length())
+  {
+    push_warning_printf(thd,
+                        Sql_condition::WARN_LEVEL_WARN,
+                        ER_ORACLE_COMPAT_FUNCTION_ERROR,
+                        ER_THD(thd, ER_ORACLE_COMPAT_FUNCTION_ERROR),
+                        warning_message.c_ptr());
+    if (!fixed_length)
+      warning_message.length(0);
+  }
+
+  null_value= 1;
+  return 0;
+}
+
 
 bool Item_func_from_unixtime::fix_length_and_dec()
 {
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index af266956b05..9b78d6c159e 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -978,6 +978,57 @@ public:
 };
 
 
+/* the max length of datetime format models string in Oracle is 144 */
+#define MAX_DATETIME_FORMAT_MODEL_LEN 144
+
+class Item_func_tochar :public Item_str_func
+{
+  const MY_LOCALE *locale;
+  THD *thd;
+  String warning_message;
+  bool fixed_length;
+
+  /*
+    When datetime format models is parsed, use uint16 integers to
+    represent the format models and store in fmt_array.
+  */
+  uint16 fmt_array[MAX_DATETIME_FORMAT_MODEL_LEN+1];
+
+  bool check_arguments() const override
+  {
+    return check_argument_types_can_return_text(1, arg_count);
+  }
+
+public:
+  Item_func_tochar(THD *thd, Item *a, Item *b):
+    Item_str_func(thd, a, b), locale(0)
+  {
+    /* NOTE: max length of warning message is 64 */
+    warning_message.alloc(64);
+    warning_message.length(0);
+  }
+  ~Item_func_tochar() { warning_message.free(); }
+  String *val_str(String *str) override;
+  LEX_CSTRING func_name_cstring() const override
+  {
+    static LEX_CSTRING name= {STRING_WITH_LEN("to_char") };
+    return name;
+  }
+  bool fix_length_and_dec() override;
+  bool parse_format_string(const String *format, uint *fmt_len);
+
+  bool check_vcol_func_processor(void *arg) override
+  {
+    if (arg_count > 2)
+      return false;
+    return mark_unsupported_function(func_name(), "()", arg, VCOL_SESSION_FUNC);
+  }
+
+  Item *get_copy(THD *thd) override
+  { return get_item_copy<Item_func_tochar>(thd, this); }
+};
+
+
 class Item_func_from_unixtime :public Item_datetimefunc
 {
   bool check_arguments() const override
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index a19c9950196..a3b4308f649 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7973,3 +7973,5 @@ ER_DATA_WAS_COMMITED_UNDER_ROLLBACK
         eng "Engine %s does not support rollback. Changes were committed during rollback call"
 ER_FUNCTION_CANNOT_BE_USED_IN_CLAUSE
         eng "Function '%s' cannot be used in the %s clause"
+ER_ORACLE_COMPAT_FUNCTION_ERROR
+        eng "Oracle compatibility function error: %s"
diff --git a/sql/sql_string.cc b/sql/sql_string.cc
index 95a57017c53..9c57bb22085 100644
--- a/sql/sql_string.cc
+++ b/sql/sql_string.cc
@@ -1275,3 +1275,15 @@ void Binary_string::shrink(size_t arg_length)
     }
   }
 }
+
+bool Binary_string::strfill(char fill, size_t len)
+{
+  if (len)
+  {
+    if (alloc(length() + len))
+      return 1;
+    memset(Ptr + str_length, fill, len);
+    str_length+= (uint32) len;
+  }
+  return 0;
+}
diff --git a/sql/sql_string.h b/sql/sql_string.h
index 8df50ac5e66..7c36a61a38a 100644
--- a/sql/sql_string.h
+++ b/sql/sql_string.h
@@ -719,6 +719,7 @@ public:
   }
   int reserve(size_t space_needed, size_t grow_by);
 
+  bool strfill(char fill, size_t len);
   inline char *prep_append(uint32 arg_length, uint32 step_alloc)
   {
     uint32 new_length= arg_length + str_length;