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