从字符串字段转换MySQL中的日期 [英] Converting a date in MySQL from string field
问题描述
我正在使用一种系统,其中日期以 dd / mm / yyyy
的格式存储为字符串。可以在SELECT查询中将其转换为 yyyy-mm-dd
(以便我可以使用 DATE_FORMAT
它)? MySQL有一个日期解析功能吗?
目前我可以想到的唯一方法是连接一堆子串,但希望有一个更简单的解决方案。
(不幸的是,我无法将字段转换为真正的日期字段,因为它是一个元表:同一列包含的字符串的不同字段的值。)
这个:
STR_TO_DATE .datestring,'%d /%m /%Y')
...将转换字符串转换成datetime数据类型。为了确保它以您想要的格式出来,请使用 DATE_FORMAT :
DATE_FORMAT(STR_TO_DATE(t.datestring,'%d /% m /%Y'),'%Y-%m-%d')
如果可以不要更改原始列上的数据类型,建议您创建视图使用 STR_TO_DATE
调用将字符串转换为DateTime数据类型。
I'm using a system where the dates are stored as strings in the format dd/mm/yyyy
. Is it possible to convert this to yyyy-mm-dd
in a SELECT query (so that I can use DATE_FORMAT
on it)? Does MySQL have a date parsing function?
Currently the only method I can think of is to concatenate a bunch of substrings, but hopefully there's a simpler solution.
(Unfortunately I can't convert the field to a true date field since it's a meta-table: the same column contains values for different fields that are just strings.)
This:
STR_TO_DATE(t.datestring, '%d/%m/%Y')
...will convert the string into a datetime datatype. To be sure that it comes out in the format you desire, use DATE_FORMAT:
DATE_FORMAT(STR_TO_DATE(t.datestring, '%d/%m/%Y'), '%Y-%m-%d')
If you can't change the datatype on the original column, I suggest creating a view that uses the STR_TO_DATE
call to convert the string to a DateTime data type.
这篇关于从字符串字段转换MySQL中的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!