CODEWITHSUNDEEP
mysqlsql
LatinCanuck
LatinCanuck

Reputation: 463

Copying a String Column with date content to a DateTime Column in MySQL

I have a table with a column called sales_date. It is a varchar type. It contains Dates in this format "2017-08-19T16:29:11+00:00".

I created a new column called sales_date_type_datetime of type DateTime. I would like to take all the data in the sales_date, convert them to a DateTime type and copy them to the new column.

I've tried these sql stmts but both were unsuccessful 

// Error Code: 1292. Incorrect datetime value: '2017-08-15T13:32:00+00:00' for column 'salse_date_type_datetime'
UPDATE sales_table 
SET   sales_date_type_datetime = sales_date;

// Error Code: 1411. Incorrect datetime value: '' for function str_to_date
UPDATE sales_date 
SET    sales_date_type_datetime = STR_TO_DATE(sales_date, '%Y-%m-%d %H:%i:%s');

Any help or guidance would be greatly appreciated.

Upvotes: 2

Views: 966

Answers (2)

LatinCanuck
LatinCanuck

Reputation: 463

fa06's answer should have worked but for some reason it didn't work for me. Perhaps it was due to my version of mySQL (5.7.12)

This SQL below ended up working for me 

   UPDATE sales_table
   SET sales_date_type_datetime =  cast(SUBSTRING(sales_date, 1, 19) as datetime);

Upvotes: 0

Fahmi
Fahmi

Reputation: 37473

Try below -

DEMO

UPDATE sales_date 
SET    sales_date_type_datetime = STR_TO_DATE(sales_date, '%Y-%m-%dT%H:%i:%s')

Upvotes: 1

Related Questions