字符串转日期,通常都是'2018-10-16' 或'2018-10-16 12:00:00'这种数字格式类型的转为日期,
当遇到 'Fri Sep 28 10:30:00 CST 2018' ,该怎么转?
其实类似,SELECT STR_TO_DATE('15-Dec-09', '%d-%b-%y') AS date;
SELECT STR_TO_DATE( 'Fri Sep 28 10:30:00 CST 2018' , '%W %M %d %H:%i:%S CST %Y') AS date;
我们只要找到每一部分的意义和对应的格式化字符(format %-codes)就可以了.
但是还是没有找到CST 对应的格式化字符,请注意
参考 https://stackoverflow.com/questions/3296725/parse-date-in-mysql
-----examples-for------
1999-12-31 2000-01-02
23:59:58.999 03:04:05 identical to
------------ ---------- -------------
%a Fri Sun LEFT(DAYNAME(d),3)
%b Dec Jan LEFT(MONTHNAME(d),3)
%c 12 1 MONTH(d)
%D 31st 2nd DAYOFMONTH(d)+st,nd,rd
%d 31 02 LPAD(DAYOFMONTH(d),0,2)
%e 31 2 DAYOFMONTH(d)
%f 999000 000000 LPAD(MICROSECOND(t),6,0)
%H 23 03 LPAD(HOUR(t),2,0)
%h 11 03
%I 11 03
%i 59 04 LPAD(MINUTE(t),2,0)
%j 365 002
%k 23 3 HOUR(t)
%l 11 3
%M December January MONTHNAME(d)
%m 12 01 LPAD(MONTH(d),2,0)
%p PM AM
%r 11:59:58 PM 03:04:05 AM
%S 58 05 LPAD(SECOND(t),2,0)
%s 58 05 LPAD(SECOND(t),2,0)
%T 23:59:58 03:04:05
%U 52 01 LPAD(WEEK(d,0),2,0)
%u 52 00 LPAD(WEEK(d,1),2,0)
%V 52 01 RIGHT(YEARWEEK(d,2),2)
%v 52 52 RIGHT(YEARWEEK(d,3),2)
%W Friday Sunday DAYNAME(d)
%w 5 0 DAYOFWEEK(d)-1
%X 1999 2000 LEFT(YEARWEEK(d,2),4)
%x 1999 1999 LEFT(YEARWEEK(d,3),4)
%Y 1999 2000 YEAR(d)
%y 99 00 RIGHT(YEAR(d),2)
%% % %
网友评论