MySQL - 将日期字符串更改为日期类型到位? [英] MySQL - Change date string to date type in place?
问题描述
我有一个表,其中包含一个date_string 栏。由于遗留原因,这是一个文本字段,其中包含d / m / YY格式的日期。例如22/11/09于2009年11月22日。
问题:如何将此列中的所有字段转换为标准MySQL日期格式(YYYY-mm-dd ),就地?
或者,如果列类型为文本,则无法更改它,如何将每个现有的基于文本的日期,并在表格中的另一列(类型日期)中创建标准日期?
感谢您的帮助。
你可能想使用 STR_TO_DATE()
功能。
SELECT STR_TO_DATE(textdate,'%d /%m /%y')FROM MyTable ...
或使用 DATE
数据类型创建另一列,并复制值:
ALTER TABLE MyTable ADD COLUMN realdate DATE;
更新MyTable SET realdate = STR_TO_DATE(textdate,'%d /%m /%y');
(Hi all. I'm new-ish to SQL and MySQL in particular. I know some PHP.)
I have a table which contains a "date_string" column. For legacy reasons, this is a text field, containing the date in d/m/YY format. e.g. "22/11/09" for 22nd November 2009.
Question: how could I convert all fields in this column to a standard MySQL date format (YYYY-mm-dd), in-place?
Or, failing being able to change it in place, given that the column type is text, how could I take each existing text-based date, and create a standard date in another column (of type 'date') within the table?
Thanks for any help.
You probably want to use the STR_TO_DATE()
function.
SELECT STR_TO_DATE(textdate, '%d/%m/%y') FROM MyTable...
Or create another column with DATE
data type and copy the values:
ALTER TABLE MyTable ADD COLUMN realdate DATE;
UPDATE MyTable SET realdate = STR_TO_DATE(textdate, '%d/%m/%y');
这篇关于MySQL - 将日期字符串更改为日期类型到位?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!