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: Invalid argument error: date format not recognized at &MM-DD in function to_char.
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: Invalid argument error: date format not recognized at axMON-DA in function to_char.
SELECT TO_CHAR(c1, 'YYYY\nMON-DAY') FROM t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at 
MON-DAY in function to_char.
SELECT TO_CHAR(c1, 'YYYY\rMON-DAY') FROM t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at 
MON-DAY in function to_char.
SELECT TO_CHAR(c1, 'YYYY分隔MON-DAY') FROM t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at 分隔MO in function to_char.
SELECT TO_CHAR(c1, 'YYYY-分隔MON-DAY') FROM t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at 分隔MO in function to_char.
select to_char(c3, 'YYYYxDDD') from t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at xDDD in function to_char.
select to_char(c3, 'YYYY&DDD') from t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at &DDD in function to_char.
select to_char(c3, 'xxYYYY-DD') from t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at xxYYYY-D in function to_char.
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: Invalid argument error: datetime format string is too long in function to_char.
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	3047	Invalid argument error: datetime format string is too long in function to_char.
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: Invalid argument error: date format not recognized at D in function to_char.
select to_char(c3, 'D') from t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at D in function to_char.
select to_char(c3, 'DS') from t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at DS in function to_char.
select to_char(c3, 'IY') from t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at IY in function to_char.
select to_char(c3, 'IYYY') from t_to_char1 where c0 = 1;
ERROR HY000: Invalid argument error: date format not recognized at IYYY in function to_char.
#
# test for first argument data type
# 
select to_char(1, 'yyyy');
ERROR HY000: Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
select to_char(1.1, 'yyyy');
ERROR HY000: Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
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: Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
SELECT TO_CHAR(c2, 'YYYY') from t_a;
ERROR HY000: Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
SELECT TO_CHAR(c3, 'YYYY') from t_a;
ERROR HY000: Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
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	3047	Invalid argument error: date format not recognized at 1111 in function to_char.
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	3047	Invalid argument error: date format not recognized at MQ-DD in function to_char.
set @@sql_mode="STRICT_ALL_TABLES";
insert into t2 select to_char(a,f) from t1;
ERROR HY000: Invalid argument error: date format not recognized at MQ-DD in function to_char.
select * from t2;
a
2021-01-24
NULL
2021-01-24
drop table t1,t2;
set @local.sql_mode=@sql_mode;
#
# MDEV-29152: Assertion failed ... upon TO_CHAR with wrong argument
#
SELECT TO_CHAR((VALUES('2022-12-12','2020-10-10')));
ERROR HY000: Illegal parameter data type row for operation 'to_char'
SELECT TO_CHAR((STR_TO_DATE('2023-01-01', '%d-%m-%Y'), 'YYYY-MM-DD') );
ERROR HY000: Illegal parameter data type row for operation 'to_char'