mySQL str_to_date()函数返回错误 [英] mySQL str_to_date() function returns error
问题描述
当尝试使用str_to_date()将我的Estimates表中的字符串日期值的列CreatedDate转换为mySQL日期格式时,我一直收到错误消息.我的数据列包含m/d/yy格式的日期(例如:1/26/16或3/3/16).
I keep receiving an error message when trying to convert a column, CreatedDate, of string date values in my Estimates table into the mySQL date format using str_to_date(). My column of data contains dates in m/d/yy format (for example: 1/26/16 or 3/3/16).
我运行了这个查询:
UPDATE Estimates
SET CreatedDate = str_to_date( CreatedDate, '%c/%e/%y' )
mySQL返回此错误消息:
mySQL is returning this error message:
Error
SQL query:
UPDATE Estimates
SET CreatedDate = str_to_date( CreatedDate, '%c/%e/%y' )
MySQL said: #1411 - Incorrect datetime value: '' for function str_to_date
我的查询出了什么问题?
What is wrong with my query?
推荐答案
清理数据的常用策略如下:
The usual strategy for cleaning up data like this is as follows:
ALTER TABLE Estimates CHANGE COLUMN CreatedDate CreatedDateString VARCHAR(255);
ALTER TABLE Estimates ADD COLUMN CreatedDate DATE
UPDATE Estimates SET CreatedDate=STR_TO_DATE(CreatedDateString, '%c/%e/%y'))
WHERE CreatedDateString IS NOT NULL AND CreatedDateString != ''
然后,当您确信一切都已正确转换时:
Then when you're confident everything got converted correctly:
ALTER TABLE Estimates DROP COLUMN CreatedDateString
适当的DATE
字段的优点是它们具有一致的格式,并且当您在它们上添加INDEX
时,数据检索非常快,即使是在范围内,例如:
The advantage to proper DATE
fields is they're in a consistent format and when you add an INDEX
on them data retrieval is very fast, even on ranges, like:
SELECT * FROM Estimates WHERE CreatedDate BETWEEN '2016-01-01' AND '2016-06-30'
这篇关于mySQL str_to_date()函数返回错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!