Debian Codegraph @

mysql-test/suite/compat/oracle/r/func_to_char.result

raw
1 set @save_sql_mode=@@sql_mode;
2 #
3 # test for datetime
4 #
5 CREATE TABLE t_to_char1(c0 int, c1 date, c2 time, c3 datetime);
6 INSERT INTO t_to_char1 VALUES (1, '1000-1-1', '00:00:00', '1000-1-1 00:00:00');
7 INSERT INTO t_to_char1 VALUES (2, '9999-12-31', '23:59:59', '9999-12-31 23:59:59');
8 INSERT INTO t_to_char1 VALUES (3, '2021-01-03', '08:30:00', '2021-01-03 08:30:00');
9 INSERT INTO t_to_char1 VALUES (4, '2021-07-03', '18:30:00', '2021-07-03 18:30:00');
10 CREATE TABLE t_to_char2(c1 timestamp);
11 INSERT INTO t_to_char2 VALUES ('1980-01-11 04:50:39');
12 INSERT INTO t_to_char2 VALUES ('2000-11-11 12:50:00');
13 INSERT INTO t_to_char2 VALUES ('2030-11-11 18:20:10');
14 SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char2;
15 TO_CHAR(c1, 'YYYY-MM-DD')
16 1980-01-11
17 2000-11-11
18 2030-11-11
19 SELECT TO_CHAR(c1, 'HH24-MI-SS') FROM t_to_char2;
20 TO_CHAR(c1, 'HH24-MI-SS')
21 04-50-39
22 12-50-00
23 18-20-10
24 #
25 # test YYYY/YY/MM/DD/HH/HH24/MI/SS
26 #
27 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;
28 C1 C2 C3
29 1000-01-01 12:00:00 00-01-01 00:00:00
30 9999-12-31 11:59:59 99-12-31 23:59:59
31 2021-01-03 08:30:00 21-01-03 08:30:00
32 2021-07-03 06:30:00 21-07-03 18:30:00
33 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;
34 C1 C2 C3
35 1000-01-01 12:00:00 00-01-01 00:00:00
36 9999-12-31 11:59:59 99-12-31 23:59:59
37 2021-01-03 08:30:00 21-01-03 08:30:00
38 2021-07-03 06:30:00 21-07-03 18:30:00
39 #
40 # test YYY/Y/MON/DD/DY/HH/HH12/MI/SS
41 #
42 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;
43 C1 C2 C3
44 000-Jan-01 12:00:00 0-January -Wed 12:00:00
45 999-Dec-31 11:59:59 9-December -Fri 11:59:59
46 021-Jan-03 08:30:00 1-January -Sun 08:30:00
47 021-Jul-03 06:30:00 1-July -Sat 06:30:00
48 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;
49 C1 C2 C3
50 000-Jan-01 12:00:00 0-January -Wed 12:00:00
51 999-Dec-31 11:59:59 9-December -Fri 11:59:59
52 021-Jan-03 08:30:00 1-January -Sun 08:30:00
53 021-Jul-03 06:30:00 1-July -Sat 06:30:00
54 #
55 # test RRRR/RR/DAY
56 #
57 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;
58 C1 C2 C3
59 1000-01-01 12:00:00 1000-01-01 00:00:00
60 9999-12-31 11:59:59 9999-12-31 23:59:59
61 2021-01-03 08:30:00 2021-01-03 08:30:00
62 2021-07-03 06:30:00 2021-07-03 18:30:00
63 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;
64 C1 C2 C3
65 00-01-01 12:00:00 00-01-01 00:00:00
66 99-12-31 11:59:59 99-12-31 23:59:59
67 21-01-03 08:30:00 21-01-03 08:30:00
68 21-07-03 06:30:00 21-07-03 18:30:00
69 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;
70 C1 C2 C3
71 1000-01-01 12:00:00 1000-01-01 00:00:00
72 9999-12-31 11:59:59 9999-12-31 23:59:59
73 2021-01-03 08:30:00 2021-01-03 08:30:00
74 2021-07-03 06:30:00 2021-07-03 18:30:00
75 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;
76 C1 C2 C3
77 00-01-01 12:00:00 00-01-01 00:00:00
78 99-12-31 11:59:59 99-12-31 23:59:59
79 21-01-03 08:30:00 21-01-03 08:30:00
80 21-07-03 06:30:00 21-07-03 18:30:00
81 #
82 # test AD/A.D./BC/B.C./AM/A.M./PM/P.M.
83 #
84 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;
85 C1 C2 C3
86 AD1000-01-01 12:00:00 AD.1000-01-01 00:00:00
87 AD9999-12-31 11:59:59 AD.9999-12-31 23:59:59
88 AD2021-01-03 08:30:00 AD.2021-01-03 08:30:00
89 AD2021-07-03 06:30:00 AD.2021-07-03 18:30:00
90 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;
91 C1 C2 C3
92 A.D.1000-01-01 12:00:00 A.D..1000-01-01 00:00:00
93 A.D.9999-12-31 11:59:59 A.D..9999-12-31 23:59:59
94 A.D.2021-01-03 08:30:00 A.D..2021-01-03 08:30:00
95 A.D.2021-07-03 06:30:00 A.D..2021-07-03 18:30:00
96 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;
97 C1 C2 C3
98 AD1000-01-01 12:00:00 AD.1000-01-01 00:00:00
99 AD9999-12-31 11:59:59 AD.9999-12-31 23:59:59
100 AD2021-01-03 08:30:00 AD.2021-01-03 08:30:00
101 AD2021-07-03 06:30:00 AD.2021-07-03 18:30:00
102 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;
103 C1 C2 C3
104 A.D.1000-01-01 12:00:00 A.D..1000-01-01 00:00:00
105 A.D.9999-12-31 11:59:59 A.D..9999-12-31 23:59:59
106 A.D.2021-01-03 08:30:00 A.D..2021-01-03 08:30:00
107 A.D.2021-07-03 06:30:00 A.D..2021-07-03 18:30:00
108 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;
109 C1 C2 C3
110 AD1000-01-01 12:00:00 AD1000-01-01 00:00:00
111 AD9999-12-31 11:59:59 AD9999-12-31 23:59:59
112 AD2021-01-03 08:30:00 AD2021-01-03 08:30:00
113 AD2021-07-03 06:30:00 AD2021-07-03 18:30:00
114 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;
115 C1 C2 C3
116 A.D.1000-01-01 12:00:00 A.D.1000-01-01 00:00:00
117 A.D.9999-12-31 11:59:59 A.D.9999-12-31 23:59:59
118 A.D.2021-01-03 08:30:00 A.D.2021-01-03 08:30:00
119 A.D.2021-07-03 06:30:00 A.D.2021-07-03 18:30:00
120 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;
121 C1 C2 C3
122 AD1000-01-01 12:00:00 AD1000-01-01 00:00:00
123 AD9999-12-31 11:59:59 AD9999-12-31 23:59:59
124 AD2021-01-03 08:30:00 AD2021-01-03 08:30:00
125 AD2021-07-03 06:30:00 AD2021-07-03 18:30:00
126 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;
127 C1 C2 C3
128 A.D.1000-01-01 12:00:00 A.D.1000-01-01 00:00:00
129 A.D.9999-12-31 11:59:59 A.D.9999-12-31 23:59:59
130 A.D.2021-01-03 08:30:00 A.D.2021-01-03 08:30:00
131 A.D.2021-07-03 06:30:00 A.D.2021-07-03 18:30:00
132 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;
133 C1 C2 C3
134 A.D.1000-01-01 AM12:00:00 A.D..1000-01-01 A.M.00:00:00
135 A.D.9999-12-31 PM11:59:59 A.D..9999-12-31 P.M.23:59:59
136 A.D.2021-01-03 AM08:30:00 A.D..2021-01-03 A.M.08:30:00
137 A.D.2021-07-03 PM06:30:00 A.D..2021-07-03 P.M.18:30:00
138 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;
139 C1 C2 C3
140 A.D.1000-01-01 AM12:00:00 A.D..1000-01-01 A.M.00:00:00
141 A.D.9999-12-31 PM11:59:59 A.D..9999-12-31 P.M.23:59:59
142 A.D.2021-01-03 AM08:30:00 A.D..2021-01-03 A.M.08:30:00
143 A.D.2021-07-03 PM06:30:00 A.D..2021-07-03 P.M.18:30:00
144 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;
145 C1 C2 C3
146 A.D.1000-01-01 AM12:00:00 A.D..1000-01-01 A.M.00:00:00
147 A.D.9999-12-31 PM11:59:59 A.D..9999-12-31 P.M.23:59:59
148 A.D.2021-01-03 AM08:30:00 A.D..2021-01-03 A.M.08:30:00
149 A.D.2021-07-03 PM06:30:00 A.D..2021-07-03 P.M.18:30:00
150 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;
151 C1 C2 C3
152 A.D.1000-01-01 AM12:00:00 A.D..1000-01-01 A.M.00:00:00
153 A.D.9999-12-31 PM11:59:59 A.D..9999-12-31 P.M.23:59:59
154 A.D.2021-01-03 AM08:30:00 A.D..2021-01-03 A.M.08:30:00
155 A.D.2021-07-03 PM06:30:00 A.D..2021-07-03 P.M.18:30:00
156 #
157 # test format without order
158 #
159 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;
160 C1 C2 C3
161 01-1000-01 12:00:00 01-00-01 00:00:00
162 12-9999-31 11:59:59 31-99-12 59:59:23
163 01-2021-03 08:00:30 03-21-01 30:00:08
164 07-2021-03 06:00:30 03-21-07 30:00:18
165 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;
166 C1 C2 C3
167 000-01-Jan 00:12:00 00:12:00 Wed-0-January
168 999-31-Dec 59:11:59 59:11:59 Fri-9-December
169 021-03-Jan 30:08:00 00:08:30 Sun-1-January
170 021-03-Jul 30:06:00 00:06:30 Sat-1-July
171 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;
172 C1 C2 C3
173 01-01-1000 00:12:00 00:1000-00-01 Jan:00
174 31-12-9999 59:11:59 59:9999-23-31 Dec:59
175 03-01-2021 00:08:30 00:2021-08-03 Jan:30
176 03-07-2021 00:06:30 00:2021-18-03 Jul:30
177 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;
178 C1 C2 C3
179 1000A.D.-01-01 12:00:00 A.D..1000-01-01 00:00:00
180 9999A.D.-12-31 11:59:59 A.D..9999-12-31 23:59:59
181 2021A.D.-01-03 08:30:00 A.D..2021-01-03 08:30:00
182 2021A.D.-07-03 06:30:00 A.D..2021-07-03 18:30:00
183 #
184 # test for special characters
185 #
186 SELECT TO_CHAR(c1, 'YYYYMMDD') AS C1, TO_CHAR(c2, 'HHMISS') AS C2, TO_CHAR(c3, 'YYMMDDHH24MISS') AS C3 FROM t_to_char1;
187 C1 C2 C3
188 10000101 120000 000101000000
189 99991231 115959 991231235959
190 20210103 083000 210103083000
191 20210703 063000 210703183000
192 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;
193 C1 C2 C3
194 1000!!01@01 12#00$00 00%01^01*00(00)00
195 9999!!12@31 11#59$59 99%12^31*23(59)59
196 2021!!01@03 08#30$00 21%01^03*08(30)00
197 2021!!07@03 06#30$00 21%07^03*18(30)00
198 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;
199 C1 C2 C3
200 1000_01+01 12=00{00 00}0101000000
201 9999_12+31 11=59{59 99}1231235959
202 2021_01+03 08=30{00 21}0103083000
203 2021_07+03 06=30{00 21}0703183000
204 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;
205 C1 C2 C3
206 1000,01.01 12/00;00 00>01<01]00[0000
207 9999,12.31 11/59;59 99>12<31]23[5959
208 2021,01.03 08/30;00 21>01<03]08[3000
209 2021,07.03 06/30;00 21>07<03]18[3000
210 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;
211 C1 C2 C3
212 1000|||0101 12&|00&|00 00&&&\01&&|01 00| 00&||abx00
213 9999|||1231 11&|59&|59 99&&&\12&&|31 23| 59&||abx59
214 2021|||0103 08&|30&|00 21&&&\01&&|03 08| 30&||abx00
215 2021|||0703 06&|30&|00 21&&&\07&&|03 18| 30&||abx00
216 SELECT TO_CHAR(c1, 'YYYY&MM-DD') FROM t_to_char1 where c0=1;
217 ERROR HY000: Invalid argument error: date format not recognized at &MM-DD in function to_char.
218 SELECT TO_CHAR(c1, 'YYYY"abx"MM"bsz"DD') AS C1 FROM t_to_char1;
219 C1
220 1000abx01bsz01
221 9999abx12bsz31
222 2021abx01bsz03
223 2021abx07bsz03
224 #
225 # test for other locale
226 #
227 SET character_set_client='utf8';
228 SET character_set_connection='utf8';
229 SET character_set_results='utf8';
230 SET lc_time_names='zh_TW';
231 SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
232 TO_CHAR(c1, 'YYYY-MON-DAY')
233 1000- 1月-週三
234 9999-12月-週五
235 2021- 1月-週日
236 2021- 7月-週六
237 SET lc_time_names='de_DE';
238 SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
239 TO_CHAR(c1, 'YYYY-MON-DAY')
240 1000-Jan-Mittwoch
241 9999-Dez-Freitag
242 2021-Jan-Sonntag
243 2021-Jul-Samstag
244 SET lc_time_names='en_US';
245 SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
246 TO_CHAR(c1, 'YYYY-MON-DAY')
247 1000-Jan-Wednesday
248 9999-Dec-Friday
249 2021-Jan-Sunday
250 2021-Jul-Saturday
251 SET lc_time_names='zh_CN';
252 SELECT TO_CHAR(c1, 'YYYY-MON-DAY') FROM t_to_char1;
253 TO_CHAR(c1, 'YYYY-MON-DAY')
254 1000- 1月-星期三
255 9999-12月-星期五
256 2021- 1月-星期日
257 2021- 7月-星期六
258 #
259 # test for invalid format
260 #
261 SELECT TO_CHAR(c1, 'YYYYaxMON-DAY') FROM t_to_char1 where c0 = 1;
262 ERROR HY000: Invalid argument error: date format not recognized at axMON-DA in function to_char.
263 SELECT TO_CHAR(c1, 'YYYY\nMON-DAY') FROM t_to_char1 where c0 = 1;
264 ERROR HY000: Invalid argument error: date format not recognized at
265 MON-DAY in function to_char.
266 SELECT TO_CHAR(c1, 'YYYY\rMON-DAY') FROM t_to_char1 where c0 = 1;
267 ERROR HY000: Invalid argument error: date format not recognized at MON-DAY in function to_char.
268 SELECT TO_CHAR(c1, 'YYYY分隔MON-DAY') FROM t_to_char1 where c0 = 1;
269 ERROR HY000: Invalid argument error: date format not recognized at 分隔MO in function to_char.
270 SELECT TO_CHAR(c1, 'YYYY-分隔MON-DAY') FROM t_to_char1 where c0 = 1;
271 ERROR HY000: Invalid argument error: date format not recognized at 分隔MO in function to_char.
272 select to_char(c3, 'YYYYxDDD') from t_to_char1 where c0 = 1;
273 ERROR HY000: Invalid argument error: date format not recognized at xDDD in function to_char.
274 select to_char(c3, 'YYYY&DDD') from t_to_char1 where c0 = 1;
275 ERROR HY000: Invalid argument error: date format not recognized at &DDD in function to_char.
276 select to_char(c3, 'xxYYYY-DD') from t_to_char1 where c0 = 1;
277 ERROR HY000: Invalid argument error: date format not recognized at xxYYYY-D in function to_char.
278 SET character_set_client='latin1';
279 SET character_set_connection='latin1';
280 SET character_set_results='latin1';
281 #
282 # test for unusual format
283 #
284 select to_char(c3, 'YYYYYYYYYYYYYYY') from t_to_char1;
285 to_char(c3, 'YYYYYYYYYYYYYYY')
286 100010001000000
287 999999999999999
288 202120212021021
289 202120212021021
290 select to_char(c3, 'YYYYYYYYYYYYYYYDDDDDD') from t_to_char1;
291 to_char(c3, 'YYYYYYYYYYYYYYYDDDDDD')
292 100010001000000010101
293 999999999999999313131
294 202120212021021030303
295 202120212021021030303
296 #
297 # oracle max length is 144
298 #
299 select to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY') from t_to_char1 where c0 = 1;
300 ERROR HY000: Invalid argument error: datetime format string is too long in function to_char.
301 CREATE TABLE t_f(c1 varchar(150));
302 insert into t_f values('YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD');
303 select to_char('2000-11-11', c1) from t_f;
304 to_char('2000-11-11', c1)
305 NULL
306 Warnings:
307 Warning 3047 Invalid argument error: datetime format string is too long in function to_char.
308 DROP TABLE t_f;
309 select to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD-MM') from t_to_char1 where c0 = 1;
310 to_char(c3, 'YYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYYY-DD-MM')
311 100010001000100010001000100010001000100010001000100010001000100010001000100010001000100010001000100010001000100010001000-01-01
312 #
313 # now only support two parameter.
314 #
315 select to_char(c3) from t_to_char1 where c0 =1;
316 to_char(c3)
317 1000-01-01 00:00:00
318 select to_char(c3, "YYYY-MM-DD HH:MI:SS") from t_to_char1 where c0 =1;
319 to_char(c3, "YYYY-MM-DD HH:MI:SS")
320 1000-01-01 12:00:00
321 select to_char(c3, "YYYY-MM-DD HH:MI:SS", "zh_CN") from t_to_char1 where c0 = 1;
322 ERROR 42000: Incorrect parameter count in the call to native function 'to_char'
323 select to_char(c3, "YYYY-MM-DD HH:MI:SS", "NLS_DATE_LANGUAGE = zh_CN") from t_to_char1 where c0 = 1;
324 ERROR 42000: Incorrect parameter count in the call to native function 'to_char'
325 #
326 # oracle support format but mariadb does not support
327 #
328 select to_char(c3, 'DDD') from t_to_char1 where c0 = 1;
329 ERROR HY000: Invalid argument error: date format not recognized at D in function to_char.
330 select to_char(c3, 'D') from t_to_char1 where c0 = 1;
331 ERROR HY000: Invalid argument error: date format not recognized at D in function to_char.
332 select to_char(c3, 'DS') from t_to_char1 where c0 = 1;
333 ERROR HY000: Invalid argument error: date format not recognized at DS in function to_char.
334 select to_char(c3, 'IY') from t_to_char1 where c0 = 1;
335 ERROR HY000: Invalid argument error: date format not recognized at IY in function to_char.
336 select to_char(c3, 'IYYY') from t_to_char1 where c0 = 1;
337 ERROR HY000: Invalid argument error: date format not recognized at IYYY in function to_char.
338 #
339 # test for first argument data type
340 #
341 select to_char(1, 'yyyy');
342 ERROR HY000: Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
343 select to_char(1.1, 'yyyy');
344 ERROR HY000: Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
345 CREATE TABLE t_a(c1 int, c2 float, c3 decimal, c4 char(20), c5 varchar(20), c6 nchar(20), c7 nvarchar(20));
346 insert into t_a VALUES (1, 3.2, 2002.02, '2000-11-11', '2000-11-11', '2000-11-11', '2000-11-11');
347 Warnings:
348 Note 1265 Data truncated for column 'c3' at row 1
349 SELECT TO_CHAR(c1, 'YYYY') from t_a;
350 ERROR HY000: Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
351 SELECT TO_CHAR(c2, 'YYYY') from t_a;
352 ERROR HY000: Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
353 SELECT TO_CHAR(c3, 'YYYY') from t_a;
354 ERROR HY000: Invalid argument error: data type of first argument must be type date/datetime/time or string in function to_char.
355 SELECT TO_CHAR(c4, 'YYYY') from t_a;
356 TO_CHAR(c4, 'YYYY')
357 2000
358 SELECT TO_CHAR(c5, 'YYYY') from t_a;
359 TO_CHAR(c5, 'YYYY')
360 2000
361 SELECT TO_CHAR(c6, 'YYYY') from t_a;
362 TO_CHAR(c6, 'YYYY')
363 2000
364 SELECT TO_CHAR(c7, 'YYYY') from t_a;
365 TO_CHAR(c7, 'YYYY')
366 2000
367 DROP TABLE t_a;
368 CREATE TABLE t_b(c0 int, c1 char(20), c2 varchar(20), c3 nchar(20), c4 nvarchar(20));
369 INSERT INTO t_b VALUES (1111, 'YYYY-MM-DD', 'YYYY-MM-DD', 'YYYY-MM-DD', 'YYYY-MM-DD');
370 SELECT TO_CHAR('2000-11-11', c0) FROM t_b;
371 TO_CHAR('2000-11-11', c0)
372 NULL
373 Warnings:
374 Warning 3047 Invalid argument error: date format not recognized at 1111 in function to_char.
375 SELECT TO_CHAR('2000-11-11', c1) FROM t_b;
376 TO_CHAR('2000-11-11', c1)
377 2000-11-11
378 SELECT TO_CHAR('2000-11-11', c2) FROM t_b;
379 TO_CHAR('2000-11-11', c2)
380 2000-11-11
381 SELECT TO_CHAR('2000-11-11', c3) FROM t_b;
382 TO_CHAR('2000-11-11', c3)
383 2000-11-11
384 SELECT TO_CHAR('2000-11-11', c4) FROM t_b;
385 TO_CHAR('2000-11-11', c4)
386 2000-11-11
387 DROP TABLE t_b;
388 EXPLAIN EXTENDED SELECT TO_CHAR(c1, 'YYYY-MM-DD') FROM t_to_char1;
389 id select_type table type possible_keys key key_len ref rows filtered Extra
390 1 SIMPLE t_to_char1 ALL NULL NULL NULL NULL 4 100.00
391 Warnings:
392 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`
393 #
394 # test for time type with date format string
395 #
396 SELECT TO_CHAR(c2, 'YYYY-MM-DD HH:MI:SS') from t_to_char1;
397 TO_CHAR(c2, 'YYYY-MM-DD HH:MI:SS')
398 0000-00-00 12:00:00
399 0000-00-00 11:59:59
400 0000-00-00 08:30:00
401 0000-00-00 06:30:00
402 SELECT TO_CHAR(c2, 'YYYY-MON-DY HH:MI:SS') from t_to_char1;
403 TO_CHAR(c2, 'YYYY-MON-DY HH:MI:SS')
404 0000-00-00 12:00:00
405 0000-00-00 11:59:59
406 0000-00-00 08:30:00
407 0000-00-00 06:30:00
408 SELECT TO_CHAR(c2, 'MON-YYYY-DY HH:MI:SS') from t_to_char1;
409 TO_CHAR(c2, 'MON-YYYY-DY HH:MI:SS')
410 00-0000-00 12:00:00
411 00-0000-00 11:59:59
412 00-0000-00 08:30:00
413 00-0000-00 06:30:00
414 SELECT TO_CHAR(c2, 'YYYY-MONTH-DAY HH:MI:SS') from t_to_char1;
415 TO_CHAR(c2, 'YYYY-MONTH-DAY HH:MI:SS')
416 0000-00-00 12:00:00
417 0000-00-00 11:59:59
418 0000-00-00 08:30:00
419 0000-00-00 06:30:00
420 DROP TABLE t_to_char1;
421 DROP TABLE t_to_char2;
422 #
423 # Test strict mode
424 #
425 create table t1 (a datetime, b int, f varchar(30)) engine=myisam;
426 insert into t1 values ("2021-01-24 19:22:10", 2014, "YYYY-MM-DD");
427 insert into t1 values ("2021-01-24 19:22:10", 2014, "YYYY-MQ-DD");
428 create table t2 (a varchar(30)) engine=myisam;
429 insert into t2 select to_char(a,f) from t1;
430 Warnings:
431 Warning 3047 Invalid argument error: date format not recognized at MQ-DD in function to_char.
432 set @@sql_mode="STRICT_ALL_TABLES";
433 insert into t2 select to_char(a,f) from t1;
434 ERROR HY000: Invalid argument error: date format not recognized at MQ-DD in function to_char.
435 select * from t2;
436 a
437 2021-01-24
438 NULL
439 2021-01-24
440 drop table t1,t2;
441 set @local.sql_mode=@sql_mode;
442 #
443 # MDEV-29152: Assertion failed ... upon TO_CHAR with wrong argument
444 #
445 SELECT TO_CHAR((VALUES('2022-12-12','2020-10-10')));
446 ERROR HY000: Illegal parameter data type row for operation 'to_char'
447 SELECT TO_CHAR((STR_TO_DATE('2023-01-01', '%d-%m-%Y'), 'YYYY-MM-DD') );
448 ERROR HY000: Illegal parameter data type row for operation 'to_char'
449 #
450 # MDEV-28136 MariaDB ASAN Unknown Crash in Item_func_to_char::parse_format_string
451 #
452 CREATE TABLE t1( a varchar(1)) ;
453 INSERT INTO t1 VALUES ('p');
454 select to_char('2000-11-11', a) from t1;
455 to_char('2000-11-11', a)
456 NULL
457 Warnings:
458 Warning 3047 Invalid argument error: date format not recognized at p in function to_char.
459 drop table t1;
460 #
461 # MDEV-36216 TO_CHAR FM format not recognized in SQL_MODE=Oracle
462 #
463 SET NAMES utf8mb3;
464 CREATE TABLE t1 (fmt VARCHAR(256));
465 INSERT INTO t1 VALUES
466 /* Add the slash character before FM to see the position of FM in the results */
467 ('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
468 ('/FMYYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
469 ('YYYY-/FMMM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
470 ('YYYY-MM-/FMDD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
471 ('YYYY-MM-DD /FMHH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
472 ('YYYY-MM-DD HH24:/FMMI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
473 ('YYYY-MM-DD HH24:MI:/FMSS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
474 ('YYYY-MM-DD HH24:MI:SS /FMDAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
475 ('YYYY-MM-DD HH24:MI:SS DAY /FMMONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH;'),
476 ('YYYY-MM-DD HH24:MI:SS DAY MONTH; /FMYYYY-MM-DD HH24:MI:SS DAY MONTH;'),
477 ('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-/FMMM-DD HH24:MI:SS DAY MONTH;'),
478 ('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-/FMDD HH24:MI:SS DAY MONTH;'),
479 ('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD /FMHH24:MI:SS DAY MONTH;'),
480 ('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:/FMMI:SS DAY MONTH;'),
481 ('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:/FMSS DAY MONTH;'),
482 ('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS /FMDAY MONTH;'),
483 ('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY /FMMONTH;'),
484 ('YYYY-MM-DD HH24:MI:SS DAY MONTH; YYYY-MM-DD HH24:MI:SS DAY MONTH/FM;'),
485 /*Formats not covered above */
486 ('YYY YY Y HH12; /FMYYY YY Y HH12;'),
487 /*FM specified multiple times*/
488 ('FMFMFMFMFMFMFMFMYYYY-MM-DD [DAY] [MONTH]'),
489 ('FMFMFMFMFMFMFMFMFMYYYY-MM-DD [DAY] [MONTH]'),
490 (
491 'YYYY-MM-DD [DAY] [MONTH]; FMYYYY-MM-DD [DAY] [MONTH]; '
492 'FMYYYY-MM-DD [DAY] [MONTH]; FMYYYY-MM-DD [DAY] [MONTH]; '
493 'FMYYYY-MM-DD [DAY] [MONTH];'
494 ),
495 /*Corner cases*/
496 ('FX') /*Unknown format starting with 'F'*/,
497 ('F') /*Unexpected end of the format string*/;
498 SET lc_time_names='en_US';
499 SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1;
500 c1
501 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 Saturday February ;
502 /1-2-3 4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
503 0001-/2-3 4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
504 0001-02-/3 4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
505 0001-02-03 /4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
506 0001-02-03 04:/5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
507 0001-02-03 04:05:/6 Saturday February; 1-2-3 4:5:6 Saturday February;
508 0001-02-03 04:05:06 /Saturday February; 1-2-3 4:5:6 Saturday February;
509 0001-02-03 04:05:06 Saturday /February; 1-2-3 4:5:6 Saturday February;
510 0001-02-03 04:05:06 Saturday February ; /1-2-3 4:5:6 Saturday February;
511 0001-02-03 04:05:06 Saturday February ; 0001-/2-3 4:5:6 Saturday February;
512 0001-02-03 04:05:06 Saturday February ; 0001-02-/3 4:5:6 Saturday February;
513 0001-02-03 04:05:06 Saturday February ; 0001-02-03 /4:5:6 Saturday February;
514 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:/5:6 Saturday February;
515 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:/6 Saturday February;
516 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 /Saturday February;
517 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 Saturday /February;
518 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 Saturday February /;
519 001 01 1 04; /1 1 1 4;
520 0001-02-03 [Saturday ] [February ]
521 1-2-3 [Saturday] [February]
522 0001-02-03 [Saturday ] [February ]; 1-2-3 [Saturday] [February]; 0001-02-03 [Saturday ] [February ]; 1-2-3 [Saturday] [February]; 0001-02-03 [Saturday ] [February ];
523 NULL
524 NULL
525 Warnings:
526 Warning 3047 Invalid argument error: date format not recognized at FX in function to_char.
527 Warning 3047 Invalid argument error: date format not recognized at F in function to_char.
528 SET sql_mode=ORACLE;
529 CREATE VIEW v1 AS SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1;
530 SHOW CREATE VIEW v1;
531 View Create View character_set_client collation_connection
532 v1 CREATE VIEW "v1" AS select to_char('0001-02-03 04:05:06',"t1"."fmt") AS "c1" from "t1" utf8mb3 utf8mb3_uca1400_ai_ci
533 SELECT * FROM v1;
534 c1
535 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 Saturday February ;
536 /1-2-3 4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
537 0001-/2-3 4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
538 0001-02-/3 4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
539 0001-02-03 /4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
540 0001-02-03 04:/5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
541 0001-02-03 04:05:/6 Saturday February; 1-2-3 4:5:6 Saturday February;
542 0001-02-03 04:05:06 /Saturday February; 1-2-3 4:5:6 Saturday February;
543 0001-02-03 04:05:06 Saturday /February; 1-2-3 4:5:6 Saturday February;
544 0001-02-03 04:05:06 Saturday February ; /1-2-3 4:5:6 Saturday February;
545 0001-02-03 04:05:06 Saturday February ; 0001-/2-3 4:5:6 Saturday February;
546 0001-02-03 04:05:06 Saturday February ; 0001-02-/3 4:5:6 Saturday February;
547 0001-02-03 04:05:06 Saturday February ; 0001-02-03 /4:5:6 Saturday February;
548 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:/5:6 Saturday February;
549 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:/6 Saturday February;
550 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 /Saturday February;
551 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 Saturday /February;
552 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 Saturday February /;
553 001 01 1 04; /1 1 1 4;
554 0001-02-03 [Saturday ] [February ]
555 1-2-3 [Saturday] [February]
556 0001-02-03 [Saturday ] [February ]; 1-2-3 [Saturday] [February]; 0001-02-03 [Saturday ] [February ]; 1-2-3 [Saturday] [February]; 0001-02-03 [Saturday ] [February ];
557 NULL
558 NULL
559 Warnings:
560 Warning 3047 Invalid argument error: date format not recognized at FX in function to_char.
561 Warning 3047 Invalid argument error: date format not recognized at F in function to_char.
562 SET sql_mode=DEFAULT;
563 SHOW CREATE VIEW v1;
564 View Create View character_set_client collation_connection
565 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select to_char('0001-02-03 04:05:06',`t1`.`fmt`) AS `c1` from `t1` utf8mb3 utf8mb3_uca1400_ai_ci
566 SELECT * FROM v1;
567 c1
568 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 Saturday February ;
569 /1-2-3 4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
570 0001-/2-3 4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
571 0001-02-/3 4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
572 0001-02-03 /4:5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
573 0001-02-03 04:/5:6 Saturday February; 1-2-3 4:5:6 Saturday February;
574 0001-02-03 04:05:/6 Saturday February; 1-2-3 4:5:6 Saturday February;
575 0001-02-03 04:05:06 /Saturday February; 1-2-3 4:5:6 Saturday February;
576 0001-02-03 04:05:06 Saturday /February; 1-2-3 4:5:6 Saturday February;
577 0001-02-03 04:05:06 Saturday February ; /1-2-3 4:5:6 Saturday February;
578 0001-02-03 04:05:06 Saturday February ; 0001-/2-3 4:5:6 Saturday February;
579 0001-02-03 04:05:06 Saturday February ; 0001-02-/3 4:5:6 Saturday February;
580 0001-02-03 04:05:06 Saturday February ; 0001-02-03 /4:5:6 Saturday February;
581 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:/5:6 Saturday February;
582 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:/6 Saturday February;
583 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 /Saturday February;
584 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 Saturday /February;
585 0001-02-03 04:05:06 Saturday February ; 0001-02-03 04:05:06 Saturday February /;
586 001 01 1 04; /1 1 1 4;
587 0001-02-03 [Saturday ] [February ]
588 1-2-3 [Saturday] [February]
589 0001-02-03 [Saturday ] [February ]; 1-2-3 [Saturday] [February]; 0001-02-03 [Saturday ] [February ]; 1-2-3 [Saturday] [February]; 0001-02-03 [Saturday ] [February ];
590 NULL
591 NULL
592 Warnings:
593 Warning 3047 Invalid argument error: date format not recognized at FX in function to_char.
594 Warning 3047 Invalid argument error: date format not recognized at F in function to_char.
595 DROP VIEW v1;
596 SET lc_time_names='zh_CN';
597 SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1;
598 c1
599 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 星期六 二月 ;
600 /1-2-3 4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
601 0001-/2-3 4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
602 0001-02-/3 4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
603 0001-02-03 /4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
604 0001-02-03 04:/5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
605 0001-02-03 04:05:/6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
606 0001-02-03 04:05:06 /星期六 二月; 1-2-3 4:5:6 星期六 二月;
607 0001-02-03 04:05:06 星期六 /二月; 1-2-3 4:5:6 星期六 二月;
608 0001-02-03 04:05:06 星期六 二月 ; /1-2-3 4:5:6 星期六 二月;
609 0001-02-03 04:05:06 星期六 二月 ; 0001-/2-3 4:5:6 星期六 二月;
610 0001-02-03 04:05:06 星期六 二月 ; 0001-02-/3 4:5:6 星期六 二月;
611 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 /4:5:6 星期六 二月;
612 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:/5:6 星期六 二月;
613 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:/6 星期六 二月;
614 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 /星期六 二月;
615 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 星期六 /二月;
616 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 星期六 二月 /;
617 001 01 1 04; /1 1 1 4;
618 0001-02-03 [星期六] [二月 ]
619 1-2-3 [星期六] [二月]
620 0001-02-03 [星期六] [二月 ]; 1-2-3 [星期六] [二月]; 0001-02-03 [星期六] [二月 ]; 1-2-3 [星期六] [二月]; 0001-02-03 [星期六] [二月 ];
621 NULL
622 NULL
623 Warnings:
624 Warning 3047 Invalid argument error: date format not recognized at FX in function to_char.
625 Warning 3047 Invalid argument error: date format not recognized at F in function to_char.
626 SET sql_mode=ORACLE;
627 CREATE VIEW v1 AS SELECT TO_CHAR('0001-02-03 04:05:06', fmt) AS c1 FROM t1;
628 SHOW CREATE VIEW v1;
629 View Create View character_set_client collation_connection
630 v1 CREATE VIEW "v1" AS select to_char('0001-02-03 04:05:06',"t1"."fmt") AS "c1" from "t1" utf8mb3 utf8mb3_uca1400_ai_ci
631 SELECT * FROM v1;
632 c1
633 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 星期六 二月 ;
634 /1-2-3 4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
635 0001-/2-3 4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
636 0001-02-/3 4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
637 0001-02-03 /4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
638 0001-02-03 04:/5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
639 0001-02-03 04:05:/6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
640 0001-02-03 04:05:06 /星期六 二月; 1-2-3 4:5:6 星期六 二月;
641 0001-02-03 04:05:06 星期六 /二月; 1-2-3 4:5:6 星期六 二月;
642 0001-02-03 04:05:06 星期六 二月 ; /1-2-3 4:5:6 星期六 二月;
643 0001-02-03 04:05:06 星期六 二月 ; 0001-/2-3 4:5:6 星期六 二月;
644 0001-02-03 04:05:06 星期六 二月 ; 0001-02-/3 4:5:6 星期六 二月;
645 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 /4:5:6 星期六 二月;
646 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:/5:6 星期六 二月;
647 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:/6 星期六 二月;
648 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 /星期六 二月;
649 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 星期六 /二月;
650 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 星期六 二月 /;
651 001 01 1 04; /1 1 1 4;
652 0001-02-03 [星期六] [二月 ]
653 1-2-3 [星期六] [二月]
654 0001-02-03 [星期六] [二月 ]; 1-2-3 [星期六] [二月]; 0001-02-03 [星期六] [二月 ]; 1-2-3 [星期六] [二月]; 0001-02-03 [星期六] [二月 ];
655 NULL
656 NULL
657 Warnings:
658 Warning 3047 Invalid argument error: date format not recognized at FX in function to_char.
659 Warning 3047 Invalid argument error: date format not recognized at F in function to_char.
660 SET sql_mode=DEFAULT;
661 SHOW CREATE VIEW v1;
662 View Create View character_set_client collation_connection
663 v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select to_char('0001-02-03 04:05:06',`t1`.`fmt`) AS `c1` from `t1` utf8mb3 utf8mb3_uca1400_ai_ci
664 SELECT * FROM v1;
665 c1
666 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 星期六 二月 ;
667 /1-2-3 4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
668 0001-/2-3 4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
669 0001-02-/3 4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
670 0001-02-03 /4:5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
671 0001-02-03 04:/5:6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
672 0001-02-03 04:05:/6 星期六 二月; 1-2-3 4:5:6 星期六 二月;
673 0001-02-03 04:05:06 /星期六 二月; 1-2-3 4:5:6 星期六 二月;
674 0001-02-03 04:05:06 星期六 /二月; 1-2-3 4:5:6 星期六 二月;
675 0001-02-03 04:05:06 星期六 二月 ; /1-2-3 4:5:6 星期六 二月;
676 0001-02-03 04:05:06 星期六 二月 ; 0001-/2-3 4:5:6 星期六 二月;
677 0001-02-03 04:05:06 星期六 二月 ; 0001-02-/3 4:5:6 星期六 二月;
678 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 /4:5:6 星期六 二月;
679 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:/5:6 星期六 二月;
680 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:/6 星期六 二月;
681 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 /星期六 二月;
682 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 星期六 /二月;
683 0001-02-03 04:05:06 星期六 二月 ; 0001-02-03 04:05:06 星期六 二月 /;
684 001 01 1 04; /1 1 1 4;
685 0001-02-03 [星期六] [二月 ]
686 1-2-3 [星期六] [二月]
687 0001-02-03 [星期六] [二月 ]; 1-2-3 [星期六] [二月]; 0001-02-03 [星期六] [二月 ]; 1-2-3 [星期六] [二月]; 0001-02-03 [星期六] [二月 ];
688 NULL
689 NULL
690 Warnings:
691 Warning 3047 Invalid argument error: date format not recognized at FX in function to_char.
692 Warning 3047 Invalid argument error: date format not recognized at F in function to_char.
693 DROP VIEW v1;
694 SET lc_time_names=DEFAULT;
695 DROP TABLE t1;