0

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

7
  • What is the type of the column in the old and the new table? – ypercubeᵀᴹ Aug 14 '19 at 17:18
  • Both of them are varchar – Oracle75 Aug 14 '19 at 17:27
  • Then why are you converting your output to date? – mustaccio Aug 14 '19 at 17:32
  • Please share table create statement and perhaps using the date format function during the select statement would work – Nawaz Sohail Aug 14 '19 at 17:40
  • create table final_data (Commit varchar(100), Author_Name varchar(100), Author_email varchar(100), Date_String varchar(50), Total_Sold int(4)); – Oracle75 Aug 14 '19 at 17:52
-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

4
  • it's more straightforward than your risky manipulation – eytienne Aug 14 '19 at 18:31
  • It worked - thank you @eytienne and thanks a lot everyone for your help. – Oracle75 Aug 14 '19 at 19:28
  • How is that even relevant?? The incoming data is not formatted correctly: "Sun Dec 14 22:49:13 2014 -0800" – Rick James Aug 14 '19 at 22:25
  • could you up vote ? (because the formatting seems good in the error message so it worked) – eytienne Aug 17 '19 at 20:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.