2
# Test of date format functions
6
drop table if exists t1;
9
SELECT variable_name, variable_value
10
FROM information_schema.global_variables
11
WHERE variable_name IN ('date_format', 'datetime_format', 'time_format')
12
ORDER BY variable_name;
14
SELECT variable_name, variable_value
15
FROM information_schema.session_variables
16
WHERE variable_name IN ('date_format', 'datetime_format', 'time_format')
17
ORDER BY variable_name;
20
# Test setting a lot of different formats to see which formats are accepted and
24
SET time_format='%H%i%s';
25
SET time_format='%H:%i:%s.%f';
26
SET time_format='%h-%i-%s.%f%p';
27
SET time_format='%h:%i:%s.%f %p';
28
SET time_format='%h:%i:%s%p';
30
SET date_format='%Y%m%d';
31
SET date_format='%Y.%m.%d';
32
SET date_format='%d.%m.%Y';
33
SET date_format='%m-%d-%Y';
35
set datetime_format= '%Y%m%d%H%i%s';
36
set datetime_format= '%Y-%m-%d %H:%i:%s';
37
set datetime_format= '%m-%d-%y %H:%i:%s.%f';
38
set datetime_format= '%d-%m-%Y %h:%i:%s%p';
39
set datetime_format= '%H:%i:%s %Y-%m-%d';
40
set datetime_format= '%H:%i:%s.%f %m-%d-%Y';
41
set datetime_format= '%h:%i:%s %p %Y-%m-%d';
42
set datetime_format= '%h:%i:%s.%f %p %Y-%m-%d';
44
SELECT variable_name, variable_value
45
FROM information_schema.session_variables
46
WHERE variable_name IN ('date_format', 'datetime_format', 'time_format')
47
ORDER BY variable_name;
50
SET time_format='%h:%i:%s';
52
SET time_format='%H %i:%s';
54
SET time_format='%H::%i:%s';
56
SET time_format='%H:%i:%s%f';
58
SET time_format='%H:%i.%f:%s';
60
SET time_format='%H:%i:%s%p';
62
SET time_format='%h:%i:%s.%f %p %Y-%m-%d';
64
SET time_format='%H%i%s.%f';
66
SET time_format='%H:%i-%s.%f';
68
SET date_format='%d.%m.%d';
70
SET datetime_format='%h.%m.%y %d.%i.%s';
72
set datetime_format= '%H:%i:%s.%f %p %Y-%m-%d';
77
set GLOBAL datetime_format= '%H:%i:%s %Y-%m-%d';
78
SET SESSION datetime_format=default;
79
select @@global.datetime_format, @@session.datetime_format;
80
SET GLOBAL datetime_format=default;
81
SET SESSION datetime_format=default;
82
select @@global.datetime_format, @@session.datetime_format;
84
SET GLOBAL date_format=default;
85
SET GLOBAL time_format=default;
86
SET GLOBAL datetime_format=default;
87
SET time_format=default;
88
SET date_format=default;
89
SET datetime_format=default;
92
# The following tests will work only when we at some point will enable
93
# dynamic changing of formats
96
# SET date_format='%d.%m.%Y';
97
# select CAST('01.01.2001' as DATE) as a;
98
# SET datetime_format='%d.%m.%Y %H.%i.%s';
99
# select CAST('01.01.2001 05.12.06' as DATETIME) as a;
100
# SET time_format='%H.%i.%s';
101
# select CAST('05.12.06' as TIME) as a;
103
# SET datetime_format='%d.%m.%Y %h:%i:%s %p';
104
# select CAST('01.01.2001 05:12:06AM' as DATETIME) as a;
105
# select CAST('01.01.2001 05:12:06 PM' as DATETIME) as a;
107
# SET time_format='%h:%i:%s %p';
108
# select CAST('05:12:06 AM' as TIME) as a;
109
# select CAST('05:12:06.1234PM' as TIME) as a;
111
# SET time_format='%h.%i.%s %p';
112
# SET date_format='%d.%m.%y';
113
# SET datetime_format='%d.%m.%y %h.%i.%s %p';
114
# select CAST('12-12-06' as DATE) as a;
116
# select adddate('01.01.97 11.59.59.000001 PM', 10);
117
# select datediff('31.12.97 11.59:59.000001 PM','01.01.98');
118
# select weekofyear('31.11.97 11:59:59.000001 PM');
119
# select makedate(1997,1);
120
# select addtime('31.12.97 11.59.59.999999 PM', '1 1.1.1.000002');
121
# select maketime(23,11,12);
122
# select timediff('01.01.97 11:59:59.000001 PM','31.12.95 11:59:59.000002 PM');
124
# SET time_format='%i:%s:%H';
125
# select cast(str_to_date('15-01-2001 12:59:59', '%d-%m-%Y %H:%i:%S') as TIME);
128
# Test of str_to_date
131
# PS doesn't support fractions of a second
132
--disable_ps_protocol
133
select str_to_date(concat('15-01-2001',' 2:59:58.999'),
134
concat('%d-%m-%Y',' ','%H:%i:%s.%f'));
135
select STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T');
138
create table t1 (date char(30), format char(30) not null);
139
insert into t1 values
140
('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'),
141
('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'),
142
('0003-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'),
143
('03-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'),
144
('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'),
145
('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'),
146
('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'),
147
('2003-01-02 12:11:12.12345 am', '%Y-%m-%d %h:%i:%S.%f%p'),
148
('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'),
149
('10:20:10', '%H:%i:%s'),
150
('10:20:10', '%h:%i:%s.%f'),
152
('10:20:10AM', '%h:%i:%s%p'),
153
('10:20:10AM', '%r'),
154
('10:20:10.44AM', '%h:%i:%s.%f%p'),
155
('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'),
156
('15 September 2001', '%d %M %Y'),
157
('15 SEPTEMB 2001', '%d %M %Y'),
158
('15 MAY 2001', '%d %b %Y'),
159
('15th May 2001', '%D %b %Y'),
160
('Sunday 15 MAY 2001', '%W %d %b %Y'),
161
('Sund 15 MAY 2001', '%W %d %b %Y'),
162
('Tuesday 00 2002', '%W %U %Y'),
163
('Thursday 53 1998', '%W %u %Y'),
164
('Sunday 01 2001', '%W %v %x'),
165
('Tuesday 52 2001', '%W %V %X'),
166
('060 2004', '%j %Y'),
167
('4 53 1998', '%w %u %Y'),
168
('15-01-2001', '%d-%m-%Y %H:%i:%S'),
169
('15-01-20', '%d-%m-%y'),
170
('15-2001-1', '%d-%Y-%c');
172
# PS doesn't support fractional seconds
173
--disable_ps_protocol
174
select date,format,str_to_date(date, format) as str_to_date from t1;
176
select date,format,concat('',str_to_date(date, format)) as con from t1;
178
select date,format,cast(str_to_date(date, format) as datetime) as datetime from t1;
179
select date,format,DATE(str_to_date(date, format)) as date2 from t1;
180
select date,format,TIME(str_to_date(date, format)) as time from t1;
181
select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1;
182
# Test small bug in %f handling
183
select concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'));
185
# Test wrong dates or converion specifiers
188
insert into t1 values
189
('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'),
190
('2003-01-02 10:11:12.123456', '%Y-%m-%d %h:%i:%S %p'),
191
('2003-01-02 10:11:12AM', '%Y-%m-%d %h:%i:%S.%f %p'),
192
('2003-01-02 10:11:12AN', '%Y-%m-%d %h:%i:%S%p'),
193
('2003-01-02 10:11:12 PM', '%y-%m-%d %H:%i:%S %p'),
194
('10:20:10AM', '%H:%i:%s%p'),
195
('15 Septembei 2001', '%d %M %Y'),
196
('15 Ju 2001', '%d %M %Y'),
197
('Sund 15 MA', '%W %d %b %Y'),
198
('Thursdai 12 1998', '%W %u %Y'),
199
('Sunday 01 2001', '%W %v %X'),
200
('Tuesday 52 2001', '%W %V %x'),
201
('Tuesday 52 2001', '%W %V %Y'),
202
('Tuesday 52 2001', '%W %u %x'),
203
('7 53 1998', '%w %u %Y'),
204
(NULL, get_format(DATE,'USA'));
205
select date,format,str_to_date(date, format) as str_to_date from t1;
206
select date,format,concat(str_to_date(date, format),'') as con from t1;
208
# Test 'maybe' date formats and 'strange but correct' results
211
insert into t1 values
212
('10:20:10AM', '%h:%i:%s'),
213
('2003-01-02 10:11:12', '%Y-%m-%d %h:%i:%S'),
214
('03-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p');
216
select date,format,str_to_date(date, format) as str_to_date from t1;
217
select date,format,concat(str_to_date(date, format),'') as con from t1;
226
select get_format(DATE, 'USA') as a;
227
select get_format(TIME, 'internal') as a;
228
select get_format(DATETIME, 'eur') as a;
229
select get_format(TIMESTAMP, 'eur') as a;
230
select get_format(DATE, 'TEST') as a;
231
select str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA'));
233
explain extended select makedate(1997,1), addtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),timediff("01.01.97 11:59:59.000001 PM","31.12.95 11:59:59.000002 PM"),cast(str_to_date("15-01-2001 12:59:59", "%d-%m-%Y %H:%i:%S") as TIME), maketime(23,11,12),microsecond("1997-12-31 23:59:59.000001");
236
# Test of date_format()
239
create table t1 (d date);
240
insert into t1 values ('2004-07-14'),('2005-07-14');
241
select date_format(d,"%d") from t1 order by 1;
244
# PS doesn't support fractional seconds
245
--disable_ps_protocol
246
select str_to_date("2003-....01ABCD-02 10:11:12.0012", "%Y-%.%m%@-%d %H:%i:%S.%f") as a;
249
create table t1 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
250
str_to_date("10:11:12.0012", "%H:%i:%S.%f") as f2,
251
str_to_date("2003-01-02", "%Y-%m-%d") as f3,
252
str_to_date("02", "%d") as f4, str_to_date("02 10", "%d %H") as f5;
257
create table t1 select "02 10" as a, "%d %H" as b;
258
select str_to_date(a,b) from t1;
259
create table t2 select str_to_date(a,b) from t1;
261
select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
262
str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2,
263
str_to_date("2003-01-02", "%Y-%m-%d") as f3,
264
str_to_date("02 10:11:12", "%d %H:%i:%S.%f") as f4,
265
str_to_date("02 10:11:12", "%d %H:%i:%S") as f5,
266
str_to_date("02 10", "%d %f") as f6;
268
select str_to_date("2003-01-02 10:11:12.0012ABCD", "%Y-%m-%d %H:%i:%S.%f") as f1,
269
addtime("-01:01:01.01 GGG", "-23:59:59.1") as f2,
270
microsecond("1997-12-31 23:59:59.01XXXX") as f3;
272
select str_to_date("2003-04-05 g", "%Y-%m-%d") as f1,
273
str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2;
277
# Test of locale dependent date format (WL#2928 Date Translation NRE)
280
select date_format('2004-01-01','%W (%a), %e %M (%b) %Y');
281
set lc_time_names=ru_RU;
283
select date_format('2004-01-01','%W (%a), %e %M (%b) %Y');
284
set lc_time_names=de_DE;
286
select date_format('2004-01-01','%W (%a), %e %M (%b) %Y');
288
set lc_time_names=en_US;
293
create table t1 (f1 datetime);
294
insert into t1 (f1) values ("2005-01-01");
295
insert into t1 (f1) values ("2005-02-01");
296
select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t1 order by date_format(f1, "%M");
302
select str_to_date( 1, NULL );
303
select str_to_date( NULL, 1 );
304
select str_to_date( 1, IF(1=1,NULL,NULL) );
308
# TIME_FORMAT using "%r" returns wrong hour using 24:00:00 in TIME column
310
# This tests that 24:00:00 does not return PM, when it should be AM.
311
# Some other values are being tested same time.
314
SELECT TIME_FORMAT("24:00:00", '%r');
315
SELECT TIME_FORMAT("00:00:00", '%r');
316
SELECT TIME_FORMAT("12:00:00", '%r');
317
SELECT TIME_FORMAT("15:00:00", '%r');
318
SELECT TIME_FORMAT("01:00:00", '%r');
319
SELECT TIME_FORMAT("25:00:00", '%r');
323
# TIME_FORMAT using "%l:%i" returns 36:00 with 24:00:00 in TIME column
325
# This tests that 24:00:00 does not change to "36:00 AM". Testing
326
# some other values same time.
329
SELECT TIME_FORMAT("00:00:00", '%l %p');
330
SELECT TIME_FORMAT("01:00:00", '%l %p');
331
SELECT TIME_FORMAT("12:00:00", '%l %p');
332
SELECT TIME_FORMAT("23:00:00", '%l %p');
333
SELECT TIME_FORMAT("24:00:00", '%l %p');
334
SELECT TIME_FORMAT("25:00:00", '%l %p');
337
# Bug#20729: Bad date_format() call makes mysql server crash
339
SELECT DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896);
342
# Bug #22029: str_to_date returning NULL
345
select str_to_date('04 /30/2004', '%m /%d/%Y');
346
select str_to_date('04/30 /2004', '%m /%d /%Y');
347
select str_to_date('04/30/2004 ', '%m/%d/%Y ');
349
--echo "End of 4.1 tests"
352
# Bug #41470: DATE_FORMAT() crashes the complete server with a valid date
355
# show that these two do not crash the server:
356
SELECT DATE_FORMAT("0000-01-01",'%W %d %M %Y') as valid_date;
357
SELECT DATE_FORMAT("0000-02-28",'%W %d %M %Y') as valid_date;
358
# show that date within the Gregorian range render correct results: (THU)
359
SELECT DATE_FORMAT("2009-01-01",'%W %d %M %Y') as valid_date;
361
--echo "End of 5.0 tests"