问题描述:
将数据类型为datetime 的某列时间类型数据“ 2000-01-21 04:51:00 ”转化为四列数据
4:51am,Friday,January 21,2000
方法一:(较繁琐)
SELECT concat(
concat(hour(date), ":", minute(date)) ,
if(hour(date)<12,'am' , 'pm'),
concat(',', dayname(date), ',', monthname(date), ',' , day(date), ',' , year(date))
)from mytable where id=1;
方法二:
SELECT concat(
date_format(date,'%l:%i'), lower(date_format(date,'%p')),
date_format(date,'%W,%M %e,%Y')
)
from mytable where id=1;
补充:字符串转datetime 先用substring_index()截取字符串,再用str_to_data()转换。