Debian Codegraph @

mysql-test/suite/engines/funcs/t/date_function.test

raw
1 --disable_warnings
2 DROP TABLE IF EXISTS t3;
3 --enable_warnings
4 CREATE TABLE t3(c1 DATE NOT NULL);
5 INSERT INTO t3 VALUES('1000-01-01');
6 INSERT INTO t3 VALUES('1999-12-31');
7 INSERT INTO t3 VALUES('2000-01-01');
8 INSERT INTO t3 VALUES('2006-12-25');
9 INSERT INTO t3 VALUES('2008-02-29');
10 SELECT DATE(c1) FROM t3;
11 DROP TABLE t3;
12 CREATE TABLE t3(c1 DATETIME NOT NULL);
13 INSERT INTO t3 VALUES('1000-01-01');
14 INSERT INTO t3 VALUES('1999-12-31');
15 INSERT INTO t3 VALUES('2000-01-01');
16 INSERT INTO t3 VALUES('2006-12-25');
17 INSERT INTO t3 VALUES('2008-02-29');
18 SELECT DATE(c1) FROM t3;
19 DROP TABLE t3;
20 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
21 INSERT IGNORE INTO t3 VALUES('1000-01-01');
22 INSERT INTO t3 VALUES('1999-12-31');
23 INSERT INTO t3 VALUES('2000-01-01');
24 INSERT INTO t3 VALUES('2006-12-25');
25 INSERT INTO t3 VALUES('2008-02-29');
26 SELECT DATE(c1) FROM t3;
27 DROP TABLE t3;
28 CREATE TABLE t3(c1 DATE NOT NULL);
29 INSERT INTO t3 VALUES('1000-01-01');
30 INSERT INTO t3 VALUES('1999-12-31');
31 INSERT INTO t3 VALUES('2000-01-01');
32 INSERT INTO t3 VALUES('2006-12-25');
33 INSERT INTO t3 VALUES('2008-02-29');
34 SELECT DAYNAME(c1) FROM t3;
35 DROP TABLE t3;
36 CREATE TABLE t3(c1 DATETIME NOT NULL);
37 INSERT INTO t3 VALUES('1000-01-01');
38 INSERT INTO t3 VALUES('1999-12-31');
39 INSERT INTO t3 VALUES('2000-01-01');
40 INSERT INTO t3 VALUES('2006-12-25');
41 INSERT INTO t3 VALUES('2008-02-29');
42 SELECT DAYNAME(c1) FROM t3;
43 DROP TABLE t3;
44 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
45 INSERT IGNORE INTO t3 VALUES('1000-01-01');
46 INSERT INTO t3 VALUES('1999-12-31');
47 INSERT INTO t3 VALUES('2000-01-01');
48 INSERT INTO t3 VALUES('2006-12-25');
49 INSERT INTO t3 VALUES('2008-02-29');
50 SELECT DAYNAME(c1) FROM t3;
51 DROP TABLE t3;
52 CREATE TABLE t3(c1 DATE NOT NULL);
53 INSERT INTO t3 VALUES('1000-01-01');
54 INSERT INTO t3 VALUES('1999-12-31');
55 INSERT INTO t3 VALUES('2000-01-01');
56 INSERT INTO t3 VALUES('2006-12-25');
57 INSERT INTO t3 VALUES('2008-02-29');
58 SELECT DAYOFMONTH(c1) FROM t3;
59 DROP TABLE t3;
60 CREATE TABLE t3(c1 DATETIME NOT NULL);
61 INSERT INTO t3 VALUES('1000-01-01');
62 INSERT INTO t3 VALUES('1999-12-31');
63 INSERT INTO t3 VALUES('2000-01-01');
64 INSERT INTO t3 VALUES('2006-12-25');
65 INSERT INTO t3 VALUES('2008-02-29');
66 SELECT DAYOFMONTH(c1) FROM t3;
67 DROP TABLE t3;
68 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
69 INSERT IGNORE INTO t3 VALUES('1000-01-01');
70 INSERT INTO t3 VALUES('1999-12-31');
71 INSERT INTO t3 VALUES('2000-01-01');
72 INSERT INTO t3 VALUES('2006-12-25');
73 INSERT INTO t3 VALUES('2008-02-29');
74 SELECT DAYOFMONTH(c1) FROM t3;
75 DROP TABLE t3;
76 CREATE TABLE t3(c1 DATE NOT NULL);
77 INSERT INTO t3 VALUES('1000-01-01');
78 INSERT INTO t3 VALUES('1999-12-31');
79 INSERT INTO t3 VALUES('2000-01-01');
80 INSERT INTO t3 VALUES('2006-12-25');
81 INSERT INTO t3 VALUES('2008-02-29');
82 SELECT DAYOFWEEK(c1) FROM t3;
83 DROP TABLE t3;
84 CREATE TABLE t3(c1 DATETIME NOT NULL);
85 INSERT INTO t3 VALUES('1000-01-01');
86 INSERT INTO t3 VALUES('1999-12-31');
87 INSERT INTO t3 VALUES('2000-01-01');
88 INSERT INTO t3 VALUES('2006-12-25');
89 INSERT INTO t3 VALUES('2008-02-29');
90 SELECT DAYOFWEEK(c1) FROM t3;
91 DROP TABLE t3;
92 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
93 INSERT IGNORE INTO t3 VALUES('1000-01-01');
94 INSERT INTO t3 VALUES('1999-12-31');
95 INSERT INTO t3 VALUES('2000-01-01');
96 INSERT INTO t3 VALUES('2006-12-25');
97 INSERT INTO t3 VALUES('2008-02-29');
98 SELECT DAYOFWEEK(c1) FROM t3;
99 DROP TABLE t3;
100 CREATE TABLE t3(c1 DATE NOT NULL);
101 INSERT INTO t3 VALUES('1000-01-01');
102 INSERT INTO t3 VALUES('1999-12-31');
103 INSERT INTO t3 VALUES('2000-01-01');
104 INSERT INTO t3 VALUES('2006-12-25');
105 INSERT INTO t3 VALUES('2008-02-29');
106 SELECT DAYOFYEAR(c1) FROM t3;
107 DROP TABLE t3;
108 CREATE TABLE t3(c1 DATETIME NOT NULL);
109 INSERT INTO t3 VALUES('1000-01-01');
110 INSERT INTO t3 VALUES('1999-12-31');
111 INSERT INTO t3 VALUES('2000-01-01');
112 INSERT INTO t3 VALUES('2006-12-25');
113 INSERT INTO t3 VALUES('2008-02-29');
114 SELECT DAYOFYEAR(c1) FROM t3;
115 DROP TABLE t3;
116 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
117 INSERT IGNORE INTO t3 VALUES('1000-01-01');
118 INSERT INTO t3 VALUES('1999-12-31');
119 INSERT INTO t3 VALUES('2000-01-01');
120 INSERT INTO t3 VALUES('2006-12-25');
121 INSERT INTO t3 VALUES('2008-02-29');
122 SELECT DAYOFYEAR(c1) FROM t3;
123 DROP TABLE t3;
124 CREATE TABLE t3(c1 DATE NOT NULL);
125 INSERT INTO t3 VALUES('1000-01-01');
126 INSERT INTO t3 VALUES('1999-12-31');
127 INSERT INTO t3 VALUES('2000-01-01');
128 INSERT INTO t3 VALUES('2006-12-25');
129 INSERT INTO t3 VALUES('2008-02-29');
130 SELECT FROM_DAYS(c1) FROM t3;
131 DROP TABLE t3;
132 CREATE TABLE t3(c1 DATETIME NOT NULL);
133 INSERT INTO t3 VALUES('1000-01-01');
134 INSERT INTO t3 VALUES('1999-12-31');
135 INSERT INTO t3 VALUES('2000-01-01');
136 INSERT INTO t3 VALUES('2006-12-25');
137 INSERT INTO t3 VALUES('2008-02-29');
138 SELECT FROM_DAYS(c1) FROM t3;
139 DROP TABLE t3;
140 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
141 INSERT IGNORE INTO t3 VALUES('1000-01-01');
142 INSERT INTO t3 VALUES('1999-12-31');
143 INSERT INTO t3 VALUES('2000-01-01');
144 INSERT INTO t3 VALUES('2006-12-25');
145 INSERT INTO t3 VALUES('2008-02-29');
146 SELECT FROM_DAYS(c1) FROM t3;
147 DROP TABLE t3;
148 CREATE TABLE t3(c1 DATE NOT NULL);
149 INSERT INTO t3 VALUES('1000-01-01');
150 INSERT INTO t3 VALUES('1999-12-31');
151 INSERT INTO t3 VALUES('2000-01-01');
152 INSERT INTO t3 VALUES('2006-12-25');
153 INSERT INTO t3 VALUES('2008-02-29');
154 SELECT LAST_DAY(c1) FROM t3;
155 DROP TABLE t3;
156 CREATE TABLE t3(c1 DATETIME NOT NULL);
157 INSERT INTO t3 VALUES('1000-01-01');
158 INSERT INTO t3 VALUES('1999-12-31');
159 INSERT INTO t3 VALUES('2000-01-01');
160 INSERT INTO t3 VALUES('2006-12-25');
161 INSERT INTO t3 VALUES('2008-02-29');
162 SELECT LAST_DAY(c1) FROM t3;
163 DROP TABLE t3;
164 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
165 INSERT IGNORE INTO t3 VALUES('1000-01-01');
166 INSERT INTO t3 VALUES('1999-12-31');
167 INSERT INTO t3 VALUES('2000-01-01');
168 INSERT INTO t3 VALUES('2006-12-25');
169 INSERT INTO t3 VALUES('2008-02-29');
170 SELECT LAST_DAY(c1) FROM t3;
171 DROP TABLE t3;
172 CREATE TABLE t3(c1 DATE NOT NULL);
173 INSERT INTO t3 VALUES('1000-01-01');
174 INSERT INTO t3 VALUES('1999-12-31');
175 INSERT INTO t3 VALUES('2000-01-01');
176 INSERT INTO t3 VALUES('2006-12-25');
177 INSERT INTO t3 VALUES('2008-02-29');
178 SELECT MONTH(c1) FROM t3;
179 DROP TABLE t3;
180 CREATE TABLE t3(c1 DATETIME NOT NULL);
181 INSERT INTO t3 VALUES('1000-01-01');
182 INSERT INTO t3 VALUES('1999-12-31');
183 INSERT INTO t3 VALUES('2000-01-01');
184 INSERT INTO t3 VALUES('2006-12-25');
185 INSERT INTO t3 VALUES('2008-02-29');
186 SELECT MONTH(c1) FROM t3;
187 DROP TABLE t3;
188 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
189 INSERT IGNORE INTO t3 VALUES('1000-01-01');
190 INSERT INTO t3 VALUES('1999-12-31');
191 INSERT INTO t3 VALUES('2000-01-01');
192 INSERT INTO t3 VALUES('2006-12-25');
193 INSERT INTO t3 VALUES('2008-02-29');
194 SELECT MONTH(c1) FROM t3;
195 DROP TABLE t3;
196 CREATE TABLE t3(c1 DATE NOT NULL);
197 INSERT INTO t3 VALUES('1000-01-01');
198 INSERT INTO t3 VALUES('1999-12-31');
199 INSERT INTO t3 VALUES('2000-01-01');
200 INSERT INTO t3 VALUES('2006-12-25');
201 INSERT INTO t3 VALUES('2008-02-29');
202 SELECT MONTHNAME(c1) FROM t3;
203 DROP TABLE t3;
204 CREATE TABLE t3(c1 DATETIME NOT NULL);
205 INSERT INTO t3 VALUES('1000-01-01');
206 INSERT INTO t3 VALUES('1999-12-31');
207 INSERT INTO t3 VALUES('2000-01-01');
208 INSERT INTO t3 VALUES('2006-12-25');
209 INSERT INTO t3 VALUES('2008-02-29');
210 SELECT MONTHNAME(c1) FROM t3;
211 DROP TABLE t3;
212 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
213 INSERT IGNORE INTO t3 VALUES('1000-01-01');
214 INSERT INTO t3 VALUES('1999-12-31');
215 INSERT INTO t3 VALUES('2000-01-01');
216 INSERT INTO t3 VALUES('2006-12-25');
217 INSERT INTO t3 VALUES('2008-02-29');
218 SELECT MONTHNAME(c1) FROM t3;
219 DROP TABLE t3;
220 CREATE TABLE t3(c1 DATE NOT NULL);
221 INSERT INTO t3 VALUES('1000-01-01');
222 INSERT INTO t3 VALUES('1999-12-31');
223 INSERT INTO t3 VALUES('2000-01-01');
224 INSERT INTO t3 VALUES('2006-12-25');
225 INSERT INTO t3 VALUES('2008-02-29');
226 SELECT QUARTER(c1) FROM t3;
227 DROP TABLE t3;
228 CREATE TABLE t3(c1 DATETIME NOT NULL);
229 INSERT INTO t3 VALUES('1000-01-01');
230 INSERT INTO t3 VALUES('1999-12-31');
231 INSERT INTO t3 VALUES('2000-01-01');
232 INSERT INTO t3 VALUES('2006-12-25');
233 INSERT INTO t3 VALUES('2008-02-29');
234 SELECT QUARTER(c1) FROM t3;
235 DROP TABLE t3;
236 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
237 INSERT IGNORE INTO t3 VALUES('1000-01-01');
238 INSERT INTO t3 VALUES('1999-12-31');
239 INSERT INTO t3 VALUES('2000-01-01');
240 INSERT INTO t3 VALUES('2006-12-25');
241 INSERT INTO t3 VALUES('2008-02-29');
242 SELECT QUARTER(c1) FROM t3;
243 DROP TABLE t3;
244 CREATE TABLE t3(c1 DATE NOT NULL);
245 INSERT INTO t3 VALUES('1000-01-01');
246 INSERT INTO t3 VALUES('1999-12-31');
247 INSERT INTO t3 VALUES('2000-01-01');
248 INSERT INTO t3 VALUES('2006-12-25');
249 INSERT INTO t3 VALUES('2008-02-29');
250 SELECT TIMESTAMP(c1) FROM t3;
251 DROP TABLE t3;
252 CREATE TABLE t3(c1 DATETIME NOT NULL);
253 INSERT INTO t3 VALUES('1000-01-01');
254 INSERT INTO t3 VALUES('1999-12-31');
255 INSERT INTO t3 VALUES('2000-01-01');
256 INSERT INTO t3 VALUES('2006-12-25');
257 INSERT INTO t3 VALUES('2008-02-29');
258 SELECT TIMESTAMP(c1) FROM t3;
259 DROP TABLE t3;
260 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
261 INSERT IGNORE INTO t3 VALUES('1000-01-01');
262 INSERT INTO t3 VALUES('1999-12-31');
263 INSERT INTO t3 VALUES('2000-01-01');
264 INSERT INTO t3 VALUES('2006-12-25');
265 INSERT INTO t3 VALUES('2008-02-29');
266 SELECT TIMESTAMP(c1) FROM t3;
267 DROP TABLE t3;
268 CREATE TABLE t3(c1 DATE NOT NULL);
269 INSERT INTO t3 VALUES('1000-01-01');
270 INSERT INTO t3 VALUES('1999-12-31');
271 INSERT INTO t3 VALUES('2000-01-01');
272 INSERT INTO t3 VALUES('2006-12-25');
273 INSERT INTO t3 VALUES('2008-02-29');
274 SELECT TO_DAYS(c1) FROM t3;
275 DROP TABLE t3;
276 CREATE TABLE t3(c1 DATETIME NOT NULL);
277 INSERT INTO t3 VALUES('1000-01-01');
278 INSERT INTO t3 VALUES('1999-12-31');
279 INSERT INTO t3 VALUES('2000-01-01');
280 INSERT INTO t3 VALUES('2006-12-25');
281 INSERT INTO t3 VALUES('2008-02-29');
282 SELECT TO_DAYS(c1) FROM t3;
283 DROP TABLE t3;
284 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
285 INSERT IGNORE INTO t3 VALUES('1000-01-01');
286 INSERT INTO t3 VALUES('1999-12-31');
287 INSERT INTO t3 VALUES('2000-01-01');
288 INSERT INTO t3 VALUES('2006-12-25');
289 INSERT INTO t3 VALUES('2008-02-29');
290 SELECT TO_DAYS(c1) FROM t3;
291 DROP TABLE t3;
292 CREATE TABLE t3(c1 DATE NOT NULL);
293 INSERT INTO t3 VALUES('1000-01-01');
294 INSERT INTO t3 VALUES('1999-12-31');
295 INSERT INTO t3 VALUES('2000-01-01');
296 INSERT INTO t3 VALUES('2006-12-25');
297 INSERT INTO t3 VALUES('2008-02-29');
298 SELECT WEEK(c1) FROM t3;
299 DROP TABLE t3;
300 CREATE TABLE t3(c1 DATETIME NOT NULL);
301 INSERT INTO t3 VALUES('1000-01-01');
302 INSERT INTO t3 VALUES('1999-12-31');
303 INSERT INTO t3 VALUES('2000-01-01');
304 INSERT INTO t3 VALUES('2006-12-25');
305 INSERT INTO t3 VALUES('2008-02-29');
306 SELECT WEEK(c1) FROM t3;
307 DROP TABLE t3;
308 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
309 INSERT IGNORE INTO t3 VALUES('1000-01-01');
310 INSERT INTO t3 VALUES('1999-12-31');
311 INSERT INTO t3 VALUES('2000-01-01');
312 INSERT INTO t3 VALUES('2006-12-25');
313 INSERT INTO t3 VALUES('2008-02-29');
314 SELECT WEEK(c1) FROM t3;
315 DROP TABLE t3;
316 CREATE TABLE t3(c1 DATE NOT NULL);
317 INSERT INTO t3 VALUES('1000-01-01');
318 INSERT INTO t3 VALUES('1999-12-31');
319 INSERT INTO t3 VALUES('2000-01-01');
320 INSERT INTO t3 VALUES('2006-12-25');
321 INSERT INTO t3 VALUES('2008-02-29');
322 SELECT WEEKDAY(c1) FROM t3;
323 DROP TABLE t3;
324 CREATE TABLE t3(c1 DATETIME NOT NULL);
325 INSERT INTO t3 VALUES('1000-01-01');
326 INSERT INTO t3 VALUES('1999-12-31');
327 INSERT INTO t3 VALUES('2000-01-01');
328 INSERT INTO t3 VALUES('2006-12-25');
329 INSERT INTO t3 VALUES('2008-02-29');
330 SELECT WEEKDAY(c1) FROM t3;
331 DROP TABLE t3;
332 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
333 INSERT IGNORE INTO t3 VALUES('1000-01-01');
334 INSERT INTO t3 VALUES('1999-12-31');
335 INSERT INTO t3 VALUES('2000-01-01');
336 INSERT INTO t3 VALUES('2006-12-25');
337 INSERT INTO t3 VALUES('2008-02-29');
338 SELECT WEEKDAY(c1) FROM t3;
339 DROP TABLE t3;
340 CREATE TABLE t3(c1 DATE NOT NULL);
341 INSERT INTO t3 VALUES('1000-01-01');
342 INSERT INTO t3 VALUES('1999-12-31');
343 INSERT INTO t3 VALUES('2000-01-01');
344 INSERT INTO t3 VALUES('2006-12-25');
345 INSERT INTO t3 VALUES('2008-02-29');
346 SELECT WEEKOFYEAR(c1) FROM t3;
347 DROP TABLE t3;
348 CREATE TABLE t3(c1 DATETIME NOT NULL);
349 INSERT INTO t3 VALUES('1000-01-01');
350 INSERT INTO t3 VALUES('1999-12-31');
351 INSERT INTO t3 VALUES('2000-01-01');
352 INSERT INTO t3 VALUES('2006-12-25');
353 INSERT INTO t3 VALUES('2008-02-29');
354 SELECT WEEKOFYEAR(c1) FROM t3;
355 DROP TABLE t3;
356 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
357 INSERT IGNORE INTO t3 VALUES('1000-01-01');
358 INSERT INTO t3 VALUES('1999-12-31');
359 INSERT INTO t3 VALUES('2000-01-01');
360 INSERT INTO t3 VALUES('2006-12-25');
361 INSERT INTO t3 VALUES('2008-02-29');
362 SELECT WEEKOFYEAR(c1) FROM t3;
363 DROP TABLE t3;
364 CREATE TABLE t3(c1 DATE NOT NULL);
365 INSERT INTO t3 VALUES('1000-01-01');
366 INSERT INTO t3 VALUES('1999-12-31');
367 INSERT INTO t3 VALUES('2000-01-01');
368 INSERT INTO t3 VALUES('2006-12-25');
369 INSERT INTO t3 VALUES('2008-02-29');
370 SELECT YEAR(c1) FROM t3;
371 DROP TABLE t3;
372 CREATE TABLE t3(c1 DATETIME NOT NULL);
373 INSERT INTO t3 VALUES('1000-01-01');
374 INSERT INTO t3 VALUES('1999-12-31');
375 INSERT INTO t3 VALUES('2000-01-01');
376 INSERT INTO t3 VALUES('2006-12-25');
377 INSERT INTO t3 VALUES('2008-02-29');
378 SELECT YEAR(c1) FROM t3;
379 DROP TABLE t3;
380 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
381 INSERT IGNORE INTO t3 VALUES('1000-01-01');
382 INSERT INTO t3 VALUES('1999-12-31');
383 INSERT INTO t3 VALUES('2000-01-01');
384 INSERT INTO t3 VALUES('2006-12-25');
385 INSERT INTO t3 VALUES('2008-02-29');
386 SELECT YEAR(c1) FROM t3;
387 DROP TABLE t3;
388 CREATE TABLE t3(c1 DATE NOT NULL);
389 INSERT INTO t3 VALUES('1000-01-01');
390 INSERT INTO t3 VALUES('1999-12-31');
391 INSERT INTO t3 VALUES('2000-01-01');
392 INSERT INTO t3 VALUES('2006-12-25');
393 INSERT INTO t3 VALUES('2008-02-29');
394 SELECT YEARWEEK(c1) FROM t3;
395 DROP TABLE t3;
396 CREATE TABLE t3(c1 DATETIME NOT NULL);
397 INSERT INTO t3 VALUES('1000-01-01');
398 INSERT INTO t3 VALUES('1999-12-31');
399 INSERT INTO t3 VALUES('2000-01-01');
400 INSERT INTO t3 VALUES('2006-12-25');
401 INSERT INTO t3 VALUES('2008-02-29');
402 SELECT YEARWEEK(c1) FROM t3;
403 DROP TABLE t3;
404 CREATE TABLE t3(c1 TIMESTAMP NOT NULL);
405 INSERT IGNORE INTO t3 VALUES('1000-01-01');
406 INSERT INTO t3 VALUES('1999-12-31');
407 INSERT INTO t3 VALUES('2000-01-01');
408 INSERT INTO t3 VALUES('2006-12-25');
409 INSERT INTO t3 VALUES('2008-02-29');
410 SELECT YEARWEEK(c1) FROM t3;
411 DROP TABLE t3;
412