1
drop table if exists t1;
2
SHOW GLOBAL VARIABLES LIKE "%e_format";
5
datetime_format %Y-%m-%d %H:%i:%s
7
SHOW SESSION VARIABLES LIKE "%e_format";
10
datetime_format %Y-%m-%d %H:%i:%s
12
SET time_format='%H%i%s';
13
SET time_format='%H:%i:%s.%f';
14
SET time_format='%h-%i-%s.%f%p';
15
SET time_format='%h:%i:%s.%f %p';
16
SET time_format='%h:%i:%s%p';
17
SET date_format='%Y%m%d';
18
SET date_format='%Y.%m.%d';
19
SET date_format='%d.%m.%Y';
20
SET date_format='%m-%d-%Y';
21
set datetime_format= '%Y%m%d%H%i%s';
22
set datetime_format= '%Y-%m-%d %H:%i:%s';
23
set datetime_format= '%m-%d-%y %H:%i:%s.%f';
24
set datetime_format= '%d-%m-%Y %h:%i:%s%p';
25
set datetime_format= '%H:%i:%s %Y-%m-%d';
26
set datetime_format= '%H:%i:%s.%f %m-%d-%Y';
27
set datetime_format= '%h:%i:%s %p %Y-%m-%d';
28
set datetime_format= '%h:%i:%s.%f %p %Y-%m-%d';
29
SHOW SESSION VARIABLES LIKE "%e_format";
32
datetime_format %h:%i:%s.%f %p %Y-%m-%d
33
time_format %h:%i:%s%p
34
SET time_format='%h:%i:%s';
35
ERROR 42000: Variable 'time_format' can't be set to the value of '%h:%i:%s'
36
SET time_format='%H %i:%s';
37
ERROR 42000: Variable 'time_format' can't be set to the value of '%H %i:%s'
38
SET time_format='%H::%i:%s';
39
ERROR 42000: Variable 'time_format' can't be set to the value of '%H::%i:%s'
40
SET time_format='%H:%i:%s%f';
41
ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i:%s%f'
42
SET time_format='%H:%i.%f:%s';
43
ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i.%f:%s'
44
SET time_format='%H:%i:%s%p';
45
ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i:%s%p'
46
SET time_format='%h:%i:%s.%f %p %Y-%m-%d';
47
ERROR 42000: Variable 'time_format' can't be set to the value of '%h:%i:%s.%f %p %Y-%m-%d'
48
SET time_format='%H%i%s.%f';
49
ERROR 42000: Variable 'time_format' can't be set to the value of '%H%i%s.%f'
50
SET time_format='%H:%i-%s.%f';
51
ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i-%s.%f'
52
SET date_format='%d.%m.%d';
53
ERROR 42000: Variable 'date_format' can't be set to the value of '%d.%m.%d'
54
SET datetime_format='%h.%m.%y %d.%i.%s';
55
ERROR 42000: Variable 'datetime_format' can't be set to the value of '%h.%m.%y %d.%i.%s'
56
set datetime_format= '%H:%i:%s.%f %p %Y-%m-%d';
57
ERROR 42000: Variable 'datetime_format' can't be set to the value of '%H:%i:%s.%f %p %Y-%m-%d'
58
set GLOBAL datetime_format= '%H:%i:%s %Y-%m-%d';
59
SET SESSION datetime_format=default;
60
select @@global.datetime_format, @@session.datetime_format;
61
@@global.datetime_format @@session.datetime_format
62
%H:%i:%s %Y-%m-%d %H:%i:%s %Y-%m-%d
63
SET GLOBAL datetime_format=default;
64
SET SESSION datetime_format=default;
65
select @@global.datetime_format, @@session.datetime_format;
66
@@global.datetime_format @@session.datetime_format
67
%Y-%m-%d %H:%i:%s %Y-%m-%d %H:%i:%s
68
SET GLOBAL date_format=default;
69
SET GLOBAL time_format=default;
70
SET GLOBAL datetime_format=default;
71
SET time_format=default;
72
SET date_format=default;
73
SET datetime_format=default;
74
select str_to_date(concat('15-01-2001',' 2:59:58.999'),
75
concat('%d-%m-%Y',' ','%H:%i:%s.%f'));
76
str_to_date(concat('15-01-2001',' 2:59:58.999'),
77
concat('%d-%m-%Y',' ','%H:%i:%s.%f'))
78
2001-01-15 02:59:58.999000
79
select STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T');
80
STR_TO_DATE('2004.12.12 22.30.61','%Y.%m.%d %T')
83
Error 1411 Incorrect time value: '22.30.61' for function str_to_date
84
create table t1 (date char(30), format char(30) not null);
86
('2003-01-02 10:11:12', '%Y-%m-%d %H:%i:%S'),
87
('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'),
88
('0003-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'),
89
('03-01-02 8:11:2.123456', '%Y-%m-%d %H:%i:%S.%#'),
90
('2003-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p'),
91
('2003-01-02 01:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f%p'),
92
('2003-01-02 02:11:12.12345AM', '%Y-%m-%d %h:%i:%S.%f %p'),
93
('2003-01-02 12:11:12.12345 am', '%Y-%m-%d %h:%i:%S.%f%p'),
94
('2003-01-02 11:11:12Pm', '%Y-%m-%d %h:%i:%S%p'),
95
('10:20:10', '%H:%i:%s'),
96
('10:20:10', '%h:%i:%s.%f'),
98
('10:20:10AM', '%h:%i:%s%p'),
100
('10:20:10.44AM', '%h:%i:%s.%f%p'),
101
('15-01-2001 12:59:58', '%d-%m-%Y %H:%i:%S'),
102
('15 September 2001', '%d %M %Y'),
103
('15 SEPTEMB 2001', '%d %M %Y'),
104
('15 MAY 2001', '%d %b %Y'),
105
('15th May 2001', '%D %b %Y'),
106
('Sunday 15 MAY 2001', '%W %d %b %Y'),
107
('Sund 15 MAY 2001', '%W %d %b %Y'),
108
('Tuesday 00 2002', '%W %U %Y'),
109
('Thursday 53 1998', '%W %u %Y'),
110
('Sunday 01 2001', '%W %v %x'),
111
('Tuesday 52 2001', '%W %V %X'),
112
('060 2004', '%j %Y'),
113
('4 53 1998', '%w %u %Y'),
114
('15-01-2001', '%d-%m-%Y %H:%i:%S'),
115
('15-01-20', '%d-%m-%y'),
116
('15-2001-1', '%d-%Y-%c');
117
select date,format,str_to_date(date, format) as str_to_date from t1;
118
date format str_to_date
119
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12
120
03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02
121
0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 08:11:02
122
03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02
123
2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12
124
2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.123450
125
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450
126
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450
127
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12
128
10:20:10 %H:%i:%s 0000-00-00 10:20:10
129
10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10
130
10:20:10 %T 0000-00-00 10:20:10
131
10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10
132
10:20:10AM %r 0000-00-00 10:20:10
133
10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000
134
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58
135
15 September 2001 %d %M %Y 2001-09-15 00:00:00
136
15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00
137
15 MAY 2001 %d %b %Y 2001-05-15 00:00:00
138
15th May 2001 %D %b %Y 2001-05-15 00:00:00
139
Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
140
Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
141
Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00
142
Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00
143
Sunday 01 2001 %W %v %x 2001-01-07 00:00:00
144
Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00
145
060 2004 %j %Y 2004-02-29 00:00:00
146
4 53 1998 %w %u %Y 1998-12-31 00:00:00
147
15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00
148
15-01-20 %d-%m-%y 2020-01-15 00:00:00
149
15-2001-1 %d-%Y-%c 2001-01-15 00:00:00
150
select date,format,concat('',str_to_date(date, format)) as con from t1;
152
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12
153
03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02
154
0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 08:11:02
155
03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02
156
2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12
157
2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.123450
158
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450
159
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450
160
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12
161
10:20:10 %H:%i:%s 0000-00-00 10:20:10
162
10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10
163
10:20:10 %T 0000-00-00 10:20:10
164
10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10
165
10:20:10AM %r 0000-00-00 10:20:10
166
10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000
167
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58
168
15 September 2001 %d %M %Y 2001-09-15 00:00:00
169
15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00
170
15 MAY 2001 %d %b %Y 2001-05-15 00:00:00
171
15th May 2001 %D %b %Y 2001-05-15 00:00:00
172
Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
173
Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
174
Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00
175
Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00
176
Sunday 01 2001 %W %v %x 2001-01-07 00:00:00
177
Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00
178
060 2004 %j %Y 2004-02-29 00:00:00
179
4 53 1998 %w %u %Y 1998-12-31 00:00:00
180
15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00
181
15-01-20 %d-%m-%y 2020-01-15 00:00:00
182
15-2001-1 %d-%Y-%c 2001-01-15 00:00:00
183
select date,format,cast(str_to_date(date, format) as datetime) as datetime from t1;
185
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02 10:11:12
186
03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02
187
0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02 08:11:02
188
03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02 08:11:02
189
2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12
190
2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 01:11:12.123450
191
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02 02:11:12.123450
192
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02 00:11:12.123450
193
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02 23:11:12
194
10:20:10 %H:%i:%s 0000-00-00 10:20:10
195
10:20:10 %h:%i:%s.%f 0000-00-00 10:20:10
196
10:20:10 %T 0000-00-00 10:20:10
197
10:20:10AM %h:%i:%s%p 0000-00-00 10:20:10
198
10:20:10AM %r 0000-00-00 10:20:10
199
10:20:10.44AM %h:%i:%s.%f%p 0000-00-00 10:20:10.440000
200
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15 12:59:58
201
15 September 2001 %d %M %Y 2001-09-15 00:00:00
202
15 SEPTEMB 2001 %d %M %Y 2001-09-15 00:00:00
203
15 MAY 2001 %d %b %Y 2001-05-15 00:00:00
204
15th May 2001 %D %b %Y 2001-05-15 00:00:00
205
Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
206
Sund 15 MAY 2001 %W %d %b %Y 2001-05-15 00:00:00
207
Tuesday 00 2002 %W %U %Y 2002-01-01 00:00:00
208
Thursday 53 1998 %W %u %Y 1998-12-31 00:00:00
209
Sunday 01 2001 %W %v %x 2001-01-07 00:00:00
210
Tuesday 52 2001 %W %V %X 2002-01-01 00:00:00
211
060 2004 %j %Y 2004-02-29 00:00:00
212
4 53 1998 %w %u %Y 1998-12-31 00:00:00
213
15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15 00:00:00
214
15-01-20 %d-%m-%y 2020-01-15 00:00:00
215
15-2001-1 %d-%Y-%c 2001-01-15 00:00:00
216
select date,format,DATE(str_to_date(date, format)) as date2 from t1;
218
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 2003-01-02
219
03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 2003-01-02
220
0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 0003-01-02
221
03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 2003-01-02
222
2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02
223
2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 2003-01-02
224
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 2003-01-02
225
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 2003-01-02
226
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 2003-01-02
227
10:20:10 %H:%i:%s 0000-00-00
228
10:20:10 %h:%i:%s.%f 0000-00-00
229
10:20:10 %T 0000-00-00
230
10:20:10AM %h:%i:%s%p 0000-00-00
231
10:20:10AM %r 0000-00-00
232
10:20:10.44AM %h:%i:%s.%f%p 0000-00-00
233
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 2001-01-15
234
15 September 2001 %d %M %Y 2001-09-15
235
15 SEPTEMB 2001 %d %M %Y 2001-09-15
236
15 MAY 2001 %d %b %Y 2001-05-15
237
15th May 2001 %D %b %Y 2001-05-15
238
Sunday 15 MAY 2001 %W %d %b %Y 2001-05-15
239
Sund 15 MAY 2001 %W %d %b %Y 2001-05-15
240
Tuesday 00 2002 %W %U %Y 2002-01-01
241
Thursday 53 1998 %W %u %Y 1998-12-31
242
Sunday 01 2001 %W %v %x 2001-01-07
243
Tuesday 52 2001 %W %V %X 2002-01-01
244
060 2004 %j %Y 2004-02-29
245
4 53 1998 %w %u %Y 1998-12-31
246
15-01-2001 %d-%m-%Y %H:%i:%S 2001-01-15
247
15-01-20 %d-%m-%y 2020-01-15
248
15-2001-1 %d-%Y-%c 2001-01-15
249
select date,format,TIME(str_to_date(date, format)) as time from t1;
251
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12
252
03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 08:11:02
253
0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02
254
03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02
255
2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12
256
2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 01:11:12.123450
257
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.123450
258
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.123450
259
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12
260
10:20:10 %H:%i:%s 10:20:10
261
10:20:10 %h:%i:%s.%f 10:20:10
263
10:20:10AM %h:%i:%s%p 10:20:10
264
10:20:10AM %r 10:20:10
265
10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000
266
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58
267
15 September 2001 %d %M %Y 00:00:00
268
15 SEPTEMB 2001 %d %M %Y 00:00:00
269
15 MAY 2001 %d %b %Y 00:00:00
270
15th May 2001 %D %b %Y 00:00:00
271
Sunday 15 MAY 2001 %W %d %b %Y 00:00:00
272
Sund 15 MAY 2001 %W %d %b %Y 00:00:00
273
Tuesday 00 2002 %W %U %Y 00:00:00
274
Thursday 53 1998 %W %u %Y 00:00:00
275
Sunday 01 2001 %W %v %x 00:00:00
276
Tuesday 52 2001 %W %V %X 00:00:00
277
060 2004 %j %Y 00:00:00
278
4 53 1998 %w %u %Y 00:00:00
279
15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00
280
15-01-20 %d-%m-%y 00:00:00
281
15-2001-1 %d-%Y-%c 00:00:00
282
select date,format,concat(TIME(str_to_date(date, format))) as time2 from t1;
284
2003-01-02 10:11:12 %Y-%m-%d %H:%i:%S 10:11:12
285
03-01-02 8:11:2.123456 %y-%m-%d %H:%i:%S.%# 08:11:02
286
0003-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02
287
03-01-02 8:11:2.123456 %Y-%m-%d %H:%i:%S.%# 08:11:02
288
2003-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 22:11:12
289
2003-01-02 01:11:12.12345AM %Y-%m-%d %h:%i:%S.%f%p 01:11:12.123450
290
2003-01-02 02:11:12.12345AM %Y-%m-%d %h:%i:%S.%f %p 02:11:12.123450
291
2003-01-02 12:11:12.12345 am %Y-%m-%d %h:%i:%S.%f%p 00:11:12.123450
292
2003-01-02 11:11:12Pm %Y-%m-%d %h:%i:%S%p 23:11:12
293
10:20:10 %H:%i:%s 10:20:10
294
10:20:10 %h:%i:%s.%f 10:20:10
296
10:20:10AM %h:%i:%s%p 10:20:10
297
10:20:10AM %r 10:20:10
298
10:20:10.44AM %h:%i:%s.%f%p 10:20:10.440000
299
15-01-2001 12:59:58 %d-%m-%Y %H:%i:%S 12:59:58
300
15 September 2001 %d %M %Y 00:00:00
301
15 SEPTEMB 2001 %d %M %Y 00:00:00
302
15 MAY 2001 %d %b %Y 00:00:00
303
15th May 2001 %D %b %Y 00:00:00
304
Sunday 15 MAY 2001 %W %d %b %Y 00:00:00
305
Sund 15 MAY 2001 %W %d %b %Y 00:00:00
306
Tuesday 00 2002 %W %U %Y 00:00:00
307
Thursday 53 1998 %W %u %Y 00:00:00
308
Sunday 01 2001 %W %v %x 00:00:00
309
Tuesday 52 2001 %W %V %X 00:00:00
310
060 2004 %j %Y 00:00:00
311
4 53 1998 %w %u %Y 00:00:00
312
15-01-2001 %d-%m-%Y %H:%i:%S 00:00:00
313
15-01-20 %d-%m-%y 00:00:00
314
15-2001-1 %d-%Y-%c 00:00:00
315
select concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'));
316
concat('',str_to_date('8:11:2.123456 03-01-02','%H:%i:%S.%f %y-%m-%d'))
317
2003-01-02 08:11:02.123456
319
insert into t1 values
320
('2003-01-02 10:11:12 PM', '%Y-%m-%d %H:%i:%S %p'),
321
('2003-01-02 10:11:12.123456', '%Y-%m-%d %h:%i:%S %p'),
322
('2003-01-02 10:11:12AM', '%Y-%m-%d %h:%i:%S.%f %p'),
323
('2003-01-02 10:11:12AN', '%Y-%m-%d %h:%i:%S%p'),
324
('2003-01-02 10:11:12 PM', '%y-%m-%d %H:%i:%S %p'),
325
('10:20:10AM', '%H:%i:%s%p'),
326
('15 Septembei 2001', '%d %M %Y'),
327
('15 Ju 2001', '%d %M %Y'),
328
('Sund 15 MA', '%W %d %b %Y'),
329
('Thursdai 12 1998', '%W %u %Y'),
330
('Sunday 01 2001', '%W %v %X'),
331
('Tuesday 52 2001', '%W %V %x'),
332
('Tuesday 52 2001', '%W %V %Y'),
333
('Tuesday 52 2001', '%W %u %x'),
334
('7 53 1998', '%w %u %Y'),
335
(NULL, get_format(DATE,'USA'));
336
select date,format,str_to_date(date, format) as str_to_date from t1;
337
date format str_to_date
338
2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL
339
2003-01-02 10:11:12.123456 %Y-%m-%d %h:%i:%S %p NULL
340
2003-01-02 10:11:12AM %Y-%m-%d %h:%i:%S.%f %p NULL
341
2003-01-02 10:11:12AN %Y-%m-%d %h:%i:%S%p NULL
342
2003-01-02 10:11:12 PM %y-%m-%d %H:%i:%S %p NULL
343
10:20:10AM %H:%i:%s%p NULL
344
15 Septembei 2001 %d %M %Y NULL
345
15 Ju 2001 %d %M %Y NULL
346
Sund 15 MA %W %d %b %Y NULL
347
Thursdai 12 1998 %W %u %Y NULL
348
Sunday 01 2001 %W %v %X NULL
349
Tuesday 52 2001 %W %V %x NULL
350
Tuesday 52 2001 %W %V %Y NULL
351
Tuesday 52 2001 %W %u %x NULL
352
7 53 1998 %w %u %Y NULL
355
Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_date
356
Error 1411 Incorrect datetime value: '2003-01-02 10:11:12.123456' for function str_to_date
357
Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AM' for function str_to_date
358
Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AN' for function str_to_date
359
Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_date
360
Error 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
361
Error 1411 Incorrect datetime value: '15 Septembei 2001' for function str_to_date
362
Error 1411 Incorrect datetime value: '15 Ju 2001' for function str_to_date
363
Error 1411 Incorrect datetime value: 'Sund 15 MA' for function str_to_date
364
Error 1411 Incorrect datetime value: 'Thursdai 12 1998' for function str_to_date
365
Error 1411 Incorrect datetime value: 'Sunday 01 2001' for function str_to_date
366
Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
367
Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
368
Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
369
Error 1411 Incorrect datetime value: '7 53 1998' for function str_to_date
370
select date,format,concat(str_to_date(date, format),'') as con from t1;
372
2003-01-02 10:11:12 PM %Y-%m-%d %H:%i:%S %p NULL
373
2003-01-02 10:11:12.123456 %Y-%m-%d %h:%i:%S %p NULL
374
2003-01-02 10:11:12AM %Y-%m-%d %h:%i:%S.%f %p NULL
375
2003-01-02 10:11:12AN %Y-%m-%d %h:%i:%S%p NULL
376
2003-01-02 10:11:12 PM %y-%m-%d %H:%i:%S %p NULL
377
10:20:10AM %H:%i:%s%p NULL
378
15 Septembei 2001 %d %M %Y NULL
379
15 Ju 2001 %d %M %Y NULL
380
Sund 15 MA %W %d %b %Y NULL
381
Thursdai 12 1998 %W %u %Y NULL
382
Sunday 01 2001 %W %v %X NULL
383
Tuesday 52 2001 %W %V %x NULL
384
Tuesday 52 2001 %W %V %Y NULL
385
Tuesday 52 2001 %W %u %x NULL
386
7 53 1998 %w %u %Y NULL
389
Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_date
390
Error 1411 Incorrect datetime value: '2003-01-02 10:11:12.123456' for function str_to_date
391
Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AM' for function str_to_date
392
Error 1411 Incorrect datetime value: '2003-01-02 10:11:12AN' for function str_to_date
393
Error 1411 Incorrect datetime value: '2003-01-02 10:11:12 PM' for function str_to_date
394
Error 1411 Incorrect datetime value: '10:20:10AM' for function str_to_date
395
Error 1411 Incorrect datetime value: '15 Septembei 2001' for function str_to_date
396
Error 1411 Incorrect datetime value: '15 Ju 2001' for function str_to_date
397
Error 1411 Incorrect datetime value: 'Sund 15 MA' for function str_to_date
398
Error 1411 Incorrect datetime value: 'Thursdai 12 1998' for function str_to_date
399
Error 1411 Incorrect datetime value: 'Sunday 01 2001' for function str_to_date
400
Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
401
Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
402
Error 1411 Incorrect datetime value: 'Tuesday 52 2001' for function str_to_date
403
Error 1411 Incorrect datetime value: '7 53 1998' for function str_to_date
405
insert into t1 values
406
('10:20:10AM', '%h:%i:%s'),
407
('2003-01-02 10:11:12', '%Y-%m-%d %h:%i:%S'),
408
('03-01-02 10:11:12 PM', '%Y-%m-%d %h:%i:%S %p');
409
select date,format,str_to_date(date, format) as str_to_date from t1;
410
date format str_to_date
411
10:20:10AM %h:%i:%s 0000-00-00 10:20:10
412
2003-01-02 10:11:12 %Y-%m-%d %h:%i:%S 2003-01-02 10:11:12
413
03-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12
415
Warning 1292 Incorrect datetime value: '10:20:10AM'
416
select date,format,concat(str_to_date(date, format),'') as con from t1;
418
10:20:10AM %h:%i:%s 0000-00-00 10:20:10
419
2003-01-02 10:11:12 %Y-%m-%d %h:%i:%S 2003-01-02 10:11:12
420
03-01-02 10:11:12 PM %Y-%m-%d %h:%i:%S %p 2003-01-02 22:11:12
422
Warning 1292 Incorrect datetime value: '10:20:10AM'
424
select get_format(DATE, 'USA') as a;
427
select get_format(TIME, 'internal') as a;
430
select get_format(DATETIME, 'eur') as a;
433
select get_format(TIMESTAMP, 'eur') as a;
436
select get_format(DATE, 'TEST') as a;
439
select str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA'));
440
str_to_date('15-01-2001 12:59:59', GET_FORMAT(DATE,'USA'))
443
Error 1411 Incorrect datetime value: '15-01-2001 12:59:59' for function str_to_date
444
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");
445
id select_type table type possible_keys key key_len ref rows filtered Extra
446
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
448
Note 1003 select makedate(1997,1) AS "makedate(1997,1)",addtime('31.12.97 11.59.59.999999 PM','1 1.1.1.000002') AS "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') AS "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') AS "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) AS "cast(str_to_date(""15-01-2001 12:59:59"", ""%d-%m-%Y %H:%i:%S"") as TIME)",maketime(23,11,12) AS "maketime(23,11,12)",microsecond('1997-12-31 23:59:59.000001') AS "microsecond(""1997-12-31 23:59:59.000001"")"
449
create table t1 (d date);
450
insert into t1 values ('2004-07-14'),('2005-07-14');
451
select date_format(d,"%d") from t1 order by 1;
456
select str_to_date("2003-....01ABCD-02 10:11:12.0012", "%Y-%.%m%@-%d %H:%i:%S.%f") as a;
458
2003-01-02 10:11:12.001200
459
create table t1 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
460
str_to_date("10:11:12.0012", "%H:%i:%S.%f") as f2,
461
str_to_date("2003-01-02", "%Y-%m-%d") as f3,
462
str_to_date("02", "%d") as f4, str_to_date("02 10", "%d %H") as f5;
464
Field Type Null Key Default Extra
472
2003-01-02 10:11:12 10:11:12 2003-01-02 0000-00-02 58:00:00
474
create table t1 select "02 10" as a, "%d %H" as b;
475
select str_to_date(a,b) from t1;
478
create table t2 select str_to_date(a,b) from t1;
480
Field Type Null Key Default Extra
481
str_to_date(a,b) datetime YES NULL
482
select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f") as f1,
483
str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S") as f2,
484
str_to_date("2003-01-02", "%Y-%m-%d") as f3,
485
str_to_date("02 10:11:12", "%d %H:%i:%S.%f") as f4,
486
str_to_date("02 10:11:12", "%d %H:%i:%S") as f5,
487
str_to_date("02 10", "%d %f") as f6;
489
2003-01-02 10:11:12.001200 2003-01-02 10:11:12 2003-01-02 58:11:12 58:11:12 48:00:00.100000
491
Warning 1292 Truncated incorrect datetime value: '2003-01-02 10:11:12.0012'
493
select str_to_date("2003-01-02 10:11:12.0012ABCD", "%Y-%m-%d %H:%i:%S.%f") as f1,
494
addtime("-01:01:01.01 GGG", "-23:59:59.1") as f2,
495
microsecond("1997-12-31 23:59:59.01XXXX") as f3;
497
2003-01-02 10:11:12.001200 -25:01:00.110000 10000
499
Warning 1292 Truncated incorrect datetime value: '2003-01-02 10:11:12.0012ABCD'
500
Warning 1292 Truncated incorrect time value: '-01:01:01.01 GGG'
501
Warning 1292 Truncated incorrect time value: '1997-12-31 23:59:59.01XXXX'
502
select str_to_date("2003-04-05 g", "%Y-%m-%d") as f1,
503
str_to_date("2003-04-05 10:11:12.101010234567", "%Y-%m-%d %H:%i:%S.%f") as f2;
505
2003-04-05 2003-04-05 10:11:12.101010
507
Warning 1292 Truncated incorrect date value: '2003-04-05 g'
508
Warning 1292 Truncated incorrect datetime value: '2003-04-05 10:11:12.101010234567'
510
select date_format('2004-01-01','%W (%a), %e %M (%b) %Y');
511
date_format('2004-01-01','%W (%a), %e %M (%b) %Y')
512
Thursday (Thu), 1 January (Jan) 2004
513
set lc_time_names=ru_RU;
515
select date_format('2004-01-01','%W (%a), %e %M (%b) %Y');
516
date_format('2004-01-01','%W (%a), %e %M (%b) %Y')
517
������� (���), 1 ������ (���) 2004
518
set lc_time_names=de_DE;
520
select date_format('2004-01-01','%W (%a), %e %M (%b) %Y');
521
date_format('2004-01-01','%W (%a), %e %M (%b) %Y')
522
Donnerstag (Do), 1 Januar (Jan) 2004
524
set lc_time_names=en_US;
525
create table t1 (f1 datetime);
526
insert into t1 (f1) values ("2005-01-01");
527
insert into t1 (f1) values ("2005-02-01");
528
select date_format(f1, "%m") as d1, date_format(f1, "%M") as d2 from t1 order by date_format(f1, "%M");
533
select str_to_date( 1, NULL );
534
str_to_date( 1, NULL )
536
select str_to_date( NULL, 1 );
537
str_to_date( NULL, 1 )
539
select str_to_date( 1, IF(1=1,NULL,NULL) );
540
str_to_date( 1, IF(1=1,NULL,NULL) )
542
SELECT TIME_FORMAT("24:00:00", '%r');
543
TIME_FORMAT("24:00:00", '%r')
545
SELECT TIME_FORMAT("00:00:00", '%r');
546
TIME_FORMAT("00:00:00", '%r')
548
SELECT TIME_FORMAT("12:00:00", '%r');
549
TIME_FORMAT("12:00:00", '%r')
551
SELECT TIME_FORMAT("15:00:00", '%r');
552
TIME_FORMAT("15:00:00", '%r')
554
SELECT TIME_FORMAT("01:00:00", '%r');
555
TIME_FORMAT("01:00:00", '%r')
557
SELECT TIME_FORMAT("25:00:00", '%r');
558
TIME_FORMAT("25:00:00", '%r')
560
SELECT TIME_FORMAT("00:00:00", '%l %p');
561
TIME_FORMAT("00:00:00", '%l %p')
563
SELECT TIME_FORMAT("01:00:00", '%l %p');
564
TIME_FORMAT("01:00:00", '%l %p')
566
SELECT TIME_FORMAT("12:00:00", '%l %p');
567
TIME_FORMAT("12:00:00", '%l %p')
569
SELECT TIME_FORMAT("23:00:00", '%l %p');
570
TIME_FORMAT("23:00:00", '%l %p')
572
SELECT TIME_FORMAT("24:00:00", '%l %p');
573
TIME_FORMAT("24:00:00", '%l %p')
575
SELECT TIME_FORMAT("25:00:00", '%l %p');
576
TIME_FORMAT("25:00:00", '%l %p')
578
SELECT DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896);
579
DATE_FORMAT('%Y-%m-%d %H:%i:%s', 1151414896)
582
Warning 1292 Incorrect datetime value: '%Y-%m-%d %H:%i:%s'
583
select str_to_date('04 /30/2004', '%m /%d/%Y');
584
str_to_date('04 /30/2004', '%m /%d/%Y')
586
select str_to_date('04/30 /2004', '%m /%d /%Y');
587
str_to_date('04/30 /2004', '%m /%d /%Y')
589
select str_to_date('04/30/2004 ', '%m/%d/%Y ');
590
str_to_date('04/30/2004 ', '%m/%d/%Y ')
added
removed
mysql-test/r/date_formats.result
