-1

I have a table in the database which stored the date as a String. I created a new column and made it a date. Now I need to move the dates from the old table into the new, but I get this error:

Warning: #1411 Incorrect datetime value: '2020-05-11 19:31' for function str_to_date

Here is the code I used.

update TABLE_NAME set dateAddedNew = date(str_to_date(dateAdded, '%m/%d/%Y'))

Here is the structure.

enter image description here

How can I convert the dateAdded into the dateAddedNew without keeping the time?

letsCode
  • 2,774
  • 1
  • 13
  • 37
  • Does this answer your question? [How to cast DATETIME as a DATE in mysql?](https://stackoverflow.com/questions/1468807/how-to-cast-datetime-as-a-date-in-mysql) – adampweb Jan 27 '21 at 18:07

2 Answers2

1

You need to change format RegExp to this (input datetime format):

update TABLE_NAME set dateAddedNew = date(str_to_date(dateAdded, '%Y-%m-%d %H:%i'))

adampweb
  • 1,135
  • 1
  • 9
  • 19
0

The datetime type is in the form, yyyy-mm-dd hh:mm:ss. Your dateAdded string column is missing the seconds (:ss) part so it doesn't quality as datetime.

Here's what I recommend assuming dateAddedNew is a date (NOT datetime) type:

update TABLE_NAME set dateAddedNew = date_format(concat(dateAdded,':00'), '%Y-%m-%d')

Just use the date_format function instead and arrange the parameters correctly in the date format. Instead of %m/%d/%Y, use a correct MySQL date format: %Y-%m-%d.

Dharman
  • 30,962
  • 25
  • 85
  • 135
Osato David Okoro
  • 17
  • 3
  • I'd also like to add that you should avoid letting your MySQL date column have a default as 0000-00-00. this causes problems with the latest MySQL versions. instead, use 1970-01-01 or at least 1000-01-01. Set it as the default when creating the column. – Osato David Okoro Jan 27 '21 at 18:16