3

My DB currently have 'date' column setup as varchar(20) and I date is formatted like this:

1/13/2015 20:00

I would like to run an update on my DB to change column type to datetime and change format of my current date to something more typical like

yyyy-mm-dd hh:mi

Can this be done in MySQL?

Chriser
  • 157
  • 1
  • 4
  • 12
  • No that not the answer @community. He need's to use [STR_TO_DATE](http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_str-to-date) function – Pரதீப் Jan 01 '16 at 03:10
  • I can see that within the comments, but would be great if someone would post full answer with [STR_TO_DATE] – Chriser Jan 01 '16 at 03:11

2 Answers2

4

Use STR_TO_DATE function

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts. If the date, time, or datetime value extracted from str is illegal, STR_TO_DATE() returns NULL and produces a warning.

To view the converted dates

select str_to_date(date_column, '%m/%d/%Y %h:%i') 
from tablename

Make sure everything is fine then run update statement 

update tablename set date_column = str_to_date(date_column, '%m/%d/%Y %h:%i')

Its better to store datetime data in datetime datatype

Alter table tablename modify column date_column datetime
Pரதீப்
  • 91,748
  • 19
  • 131
  • 172
0

Yes, it can be done in mysql. Since your existing data is not compatible with mysql's datetime format, you can't do it in one step, though.

  1. Change your text data to mysql's native datetime format (yyyy-mm-dd hh:mm:ss) using str_to_date() function in an update statement.
  2. Use ALTER TABLE ... MODIFY COLUMN ... command to change the data type of the column to datetime.

Warning This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE (see Section 5.1.7, “Server SQL Modes”).

The 1st step is to take care of data conversion issues, however, it is good practice to enable strict sql mode before the 2nd step. If you are unsure, then create another datetime column and update its values using str_to_date() to see the original and converted values side by side.

Shadow
  • 33,525
  • 10
  • 51
  • 64