Why do I get "Truncated incorrect date value" when using str_to_date()?

Question

I have data in one of my tables as "Sun Dec 14 22:49:13 2014 -0800" and need to load it into another table but skipping the time filed i.e. HH:MM:SS.

I used:

insert into final_data
select Book_Name, Author_Name, Author_email,
str_to_date(substring_index(date_string,' ',4),'%a %b %d %Y'),
Total_Sold
from data;

ERROR 1292 (22007): Truncated incorrect date value: 'Sun Dec 14 22:49:13'

I can use str_to_date(substring_index(date_string,' ',5),'%a %b %d %T %Y') but that will add HH:MM:SS in the column as well. Is there a way to load the data skipping HH:MM:SS ? i.e instead of 2014-12-14 22:49:13 it diplays 2014-12-14

Answer 1

you have to pass your datetime column into date() function like this :

create table test (
  id int AUTO_INCREMENT,
  datetime_string datetime,
  date_date date,
  PRIMARY KEY (id)
);

insert into test(datetime_string) values('2014-12-14 22:49:13');
update test set date_date = date(datetime_string);

http://sqlfiddle.com/#!9/cc200b/1