date_formats.result

这是linux下运行的mysql软件包,可用于linux 下安装 php + mysql + apach 的网络配置

drop table if exists t1;
SHOW GLOBAL VARIABLES LIKE "%_format%";
Variable_name	Value
date_format	%d.%m.%Y
datetime_format	%Y-%m-%d %H:%i:%s
default_week_format	0
time_format	%H.%i.%s
SHOW SESSION VARIABLES LIKE "%_format%";
Variable_name	Value
date_format	%d.%m.%Y
datetime_format	%Y-%m-%d %H:%i:%s
default_week_format	0
time_format	%H.%i.%s
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
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'
SET date_format='%d.%m.%d';
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
SET GLOBAL date_format=default;
SET GLOBAL time_format=default;
SET GLOBAL datetime_format=default;
SET time_format=default;
SET date_format=default;
SET datetime_format=default;
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'))
2001-01-15 02:59:58.999000
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
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'),
('03-01-02 8:11:2.123456', '%y-%m-%d %H:%i:%S.%#'),
('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'),
('10:20:10', '%T'),
('10:20:10AM', '%h:%i:%s%p'),
('10:20:10AM', '%r'),
('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'),
('15th May 2001', '%D %b %Y'),
('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'),
('Sunday 01 2001', '%W %v %x'),
('Tuesday 52 2001', '%W %V %X'),
('060 2004', '%j %Y'),
('4 53 1998', '%w %u %Y'),
('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
03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
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
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
10:20:10	%T	0000-00-00 10:20:10
10:20:10AM	%h:%i:%s%p	0000-00-00 10:20:10
10:20:10AM	%r	0000-00-00 10:20:10
10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.440000
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
15 SEPTEMB 2001	%d %M %Y	2001-09-15 00:00:00
15 MAY 2001	%d %b %Y	2001-05-15 00:00:00
15th May 2001	%D %b %Y	2001-05-15 00:00:00
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
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
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
03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
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
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
10:20:10	%T	0000-00-00 10:20:10
10:20:10AM	%h:%i:%s%p	0000-00-00 10:20:10
10:20:10AM	%r	0000-00-00 10:20:10
10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.440000
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
15 SEPTEMB 2001	%d %M %Y	2001-09-15 00:00:00
15 MAY 2001	%d %b %Y	2001-05-15 00:00:00
15th May 2001	%D %b %Y	2001-05-15 00:00:00
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
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
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
03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02 08:11:02
2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	2003-01-02 22:11:12
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
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
10:20:10	%T	0000-00-00 10:20:10
10:20:10AM	%h:%i:%s%p	0000-00-00 10:20:10
10:20:10AM	%r	0000-00-00 10:20:10
10:20:10.44AM	%h:%i:%s.%f%p	0000-00-00 10:20:10.440000
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
15 SEPTEMB 2001	%d %M %Y	2001-09-15 00:00:00
15 MAY 2001	%d %b %Y	2001-05-15 00:00:00
15th May 2001	%D %b %Y	2001-05-15 00:00:00
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
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
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,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
03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	2003-01-02
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
10:20:10	%T	0000-00-00
10:20:10AM	%h:%i:%s%p	0000-00-00
10:20:10AM	%r	0000-00-00
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
15 SEPTEMB 2001	%d %M %Y	2001-09-15
15 MAY 2001	%d %b %Y	2001-05-15
15th May 2001	%D %b %Y	2001-05-15
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
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
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
03-01-02 8:11:2.123456	%y-%m-%d %H:%i:%S.%#	08:11:02
2003-01-02 10:11:12 PM	%Y-%m-%d %h:%i:%S %p	22:11:12
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
2003-01-02 11:11:12Pm	%Y-%m-%d %h:%i:%S%p	23:11:12
10:20:10	%H:%i:%s	10:20:10
10:20:10	%h:%i:%s.%f	10:20:10
10:20:10	%T	10:20:10
10:20:10AM	%h:%i:%s%p	10:20:10
10:20:10AM	%r	10:20:10
10:20:10.44AM	%h:%i:%s.%f%p	10:20:10.440000
15-01-2001 12:59:58	%d-%m-%Y %H:%i:%S	12:59:58
15 September 2001	%d %M %Y	00:00:00