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).
尝试使用str_to_date()将我的Estimates表中的字符串日期值的列CreatedDate转换为mySQL日期格式时,我一直收到错误消息。我的数据列包含m / d / yy格式的日期(例如:1/26/16或3/3/16)。
I ran this query:
我运行了这个查询:
UPDATE Estimates
SET CreatedDate = str_to_date( CreatedDate, '%c/%e/%y' )
mySQL is returning this error message:
mySQL返回此错误消息:
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?
我的查询有什么问题?
3 个解决方案
#1
2
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
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:
适当的DATE字段的优点是它们具有一致的格式,当您在它们上添加INDEX时,数据检索非常快,即使在范围上,例如:
SELECT * FROM Estimates WHERE CreatedDate BETWEEN '2016-01-01' AND '2016-06-30'
#2
2
It's hitting blank values in your column.
它会在您的列中显示空白值。
SET CreatedDate = str_to_date( '', '%c/%e/%y' )
I think this outputs 0000-00-00 and that works as an invalid date if you are setting a date field to that.
我认为这会输出稿件,如果你设置一个日期字段,它将作为无效日期。
SET CreatedDate = STR_TO_DATE( IFNULL(case when CreatedDate = '' then null else createddate end,'1901-1-1'), '%c/%e/%y' )
That will leave 1901-01-01 values for nulls and blank
这将为空值留下1901-01-01值并留空
Added to tadman:
添加到tadman:
SET CreatedDate = STR_TO_DATE(case when CreatedDate = '' then null else createddate end, '%c/%e/%y' )
Nulls instead of 1901-01-01 if you prefer.
如果您愿意,可以使用Null而不是1901-01-01。
#3
0
Disable NO_ZERO_DATE
SQL mode:
禁用NO_ZERO_DATE SQL模式:
set @old_sql_mode = @@sql_mode;
set sql_mode = '';
Run your statement:
运行你的陈述:
UPDATE Estimates
SET CreatedDate = NULLIF(str_to_date(CreatedDate, '%c/%e/%y'), FROM_DAYS(0))
Then enable original SQL modes:
然后启用原始SQL模式:
set sql_mode = @old_sql_mode;
Disabling NO_ZERO_DATE
mode will make STR_TO_DATE
return zero date 0000-00-00
for invalid date strings, the same value is returned by FROM_DAYS(0)
. So NULLIF
will convert zero dates to NULL
.
对于无效的日期字符串,禁用NO_ZERO_DATE模式将使STR_TO_DATE返回零date 0000-00-00,FROM_DAYS(0)返回相同的值。所以NULLIF会将零日期转换为NULL。
This answer was helpful.
这个答案很有帮助。
#1
2
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
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:
适当的DATE字段的优点是它们具有一致的格式,当您在它们上添加INDEX时,数据检索非常快,即使在范围上,例如:
SELECT * FROM Estimates WHERE CreatedDate BETWEEN '2016-01-01' AND '2016-06-30'
#2
2
It's hitting blank values in your column.
它会在您的列中显示空白值。
SET CreatedDate = str_to_date( '', '%c/%e/%y' )
I think this outputs 0000-00-00 and that works as an invalid date if you are setting a date field to that.
我认为这会输出稿件,如果你设置一个日期字段,它将作为无效日期。
SET CreatedDate = STR_TO_DATE( IFNULL(case when CreatedDate = '' then null else createddate end,'1901-1-1'), '%c/%e/%y' )
That will leave 1901-01-01 values for nulls and blank
这将为空值留下1901-01-01值并留空
Added to tadman:
添加到tadman:
SET CreatedDate = STR_TO_DATE(case when CreatedDate = '' then null else createddate end, '%c/%e/%y' )
Nulls instead of 1901-01-01 if you prefer.
如果您愿意,可以使用Null而不是1901-01-01。
#3
0
Disable NO_ZERO_DATE
SQL mode:
禁用NO_ZERO_DATE SQL模式:
set @old_sql_mode = @@sql_mode;
set sql_mode = '';
Run your statement:
运行你的陈述:
UPDATE Estimates
SET CreatedDate = NULLIF(str_to_date(CreatedDate, '%c/%e/%y'), FROM_DAYS(0))
Then enable original SQL modes:
然后启用原始SQL模式:
set sql_mode = @old_sql_mode;
Disabling NO_ZERO_DATE
mode will make STR_TO_DATE
return zero date 0000-00-00
for invalid date strings, the same value is returned by FROM_DAYS(0)
. So NULLIF
will convert zero dates to NULL
.
对于无效的日期字符串,禁用NO_ZERO_DATE模式将使STR_TO_DATE返回零date 0000-00-00,FROM_DAYS(0)返回相同的值。所以NULLIF会将零日期转换为NULL。
This answer was helpful.
这个答案很有帮助。