1
1
drop table if exists t1;
2
SHOW GLOBAL VARIABLES LIKE "%e_format";
5
datetime_format %Y-%m-%d %H:%i:%s
6
innodb_file_format Antelope
8
SHOW SESSION VARIABLES LIKE "%e_format";
11
datetime_format %Y-%m-%d %H:%i:%s
12
innodb_file_format Antelope
14
SET time_format='%H%i%s';
15
SET time_format='%H:%i:%s.%f';
16
SET time_format='%h-%i-%s.%f%p';
17
SET time_format='%h:%i:%s.%f %p';
18
SET time_format='%h:%i:%s%p';
19
SET date_format='%Y%m%d';
20
SET date_format='%Y.%m.%d';
21
SET date_format='%d.%m.%Y';
22
SET date_format='%m-%d-%Y';
23
set datetime_format= '%Y%m%d%H%i%s';
24
set datetime_format= '%Y-%m-%d %H:%i:%s';
25
set datetime_format= '%m-%d-%y %H:%i:%s.%f';
26
set datetime_format= '%d-%m-%Y %h:%i:%s%p';
27
set datetime_format= '%H:%i:%s %Y-%m-%d';
28
set datetime_format= '%H:%i:%s.%f %m-%d-%Y';
29
set datetime_format= '%h:%i:%s %p %Y-%m-%d';
30
set datetime_format= '%h:%i:%s.%f %p %Y-%m-%d';
31
SHOW SESSION VARIABLES LIKE "%e_format";
34
datetime_format %h:%i:%s.%f %p %Y-%m-%d
35
innodb_file_format Antelope
36
time_format %h:%i:%s%p
37
SET time_format='%h:%i:%s';
38
ERROR 42000: Variable 'time_format' can't be set to the value of '%h:%i:%s'
39
SET time_format='%H %i:%s';
40
ERROR 42000: Variable 'time_format' can't be set to the value of '%H %i:%s'
41
SET time_format='%H::%i:%s';
42
ERROR 42000: Variable 'time_format' can't be set to the value of '%H::%i:%s'
43
SET time_format='%H:%i:%s%f';
44
ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i:%s%f'
45
SET time_format='%H:%i.%f:%s';
46
ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i.%f:%s'
47
SET time_format='%H:%i:%s%p';
48
ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i:%s%p'
49
SET time_format='%h:%i:%s.%f %p %Y-%m-%d';
50
ERROR 42000: Variable 'time_format' can't be set to the value of '%h:%i:%s.%f %p %Y-%m-%d'
51
SET time_format='%H%i%s.%f';
52
ERROR 42000: Variable 'time_format' can't be set to the value of '%H%i%s.%f'
53
SET time_format='%H:%i-%s.%f';
54
ERROR 42000: Variable 'time_format' can't be set to the value of '%H:%i-%s.%f'
55
SET date_format='%d.%m.%d';
56
ERROR 42000: Variable 'date_format' can't be set to the value of '%d.%m.%d'
57
SET datetime_format='%h.%m.%y %d.%i.%s';
58
ERROR 42000: Variable 'datetime_format' can't be set to the value of '%h.%m.%y %d.%i.%s'
59
set datetime_format= '%H:%i:%s.%f %p %Y-%m-%d';
60
ERROR 42000: Variable 'datetime_format' can't be set to the value of '%H:%i:%s.%f %p %Y-%m-%d'
61
set GLOBAL datetime_format= '%H:%i:%s %Y-%m-%d';
62
SET SESSION datetime_format=default;
63
select @@global.datetime_format, @@session.datetime_format;
64
@@global.datetime_format @@session.datetime_format
65
%H:%i:%s %Y-%m-%d %H:%i:%s %Y-%m-%d
66
SET GLOBAL datetime_format=default;
67
SET SESSION datetime_format=default;
68
select @@global.datetime_format, @@session.datetime_format;
69
@@global.datetime_format @@session.datetime_format
70
%Y-%m-%d %H:%i:%s %Y-%m-%d %H:%i:%s
71
SET GLOBAL date_format=default;
72
SET GLOBAL time_format=default;
73
SET GLOBAL datetime_format=default;
74
SET time_format=default;
75
SET date_format=default;
76
SET datetime_format=default;
77
select get_format(DATE, 'USA') as a;
80
select get_format(DATETIME, 'eur') as a;
83
select get_format(TIMESTAMP, 'eur') as a;
86
select get_format(DATE, 'TEST') as a;
89
explain extended select makedate(1997,1), subtime("31.12.97 11.59.59.999999 PM", "1 1.1.1.000002"),microsecond("1997-12-31 23:59:59.000001");
2
explain extended select makedate(1997,1), microsecond("1997-12-31 23:59:59.000001");
90
3
id select_type table type possible_keys key key_len ref rows filtered Extra
91
4
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used
93
Note 1003 select makedate(1997,1) AS `makedate(1997,1)`,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")`,microsecond('1997-12-31 23:59:59.000001') AS `microsecond("1997-12-31 23:59:59.000001")`
6
Note 1003 select makedate(1997,1) AS `makedate(1997,1)`,microsecond('1997-12-31 23:59:59.000001') AS `microsecond("1997-12-31 23:59:59.000001")`
94
7
create table t1 (d date);
95
8
insert into t1 values ('2004-07-14'),('2005-07-14');
96
9
select date_format(d,"%d") from t1 order by 1;