How to convert varchar to datetime, not working

I have imported some columns from a textfile into my table stuff.

I had to import date as varchar(255). I want to convert datatype from varchar to datetime.

The format of date data is:

6/2/2018 3:00:00 PM
6/2/2018 4:00:00 PM
6/2/2018 5:00:00 PM

I have tried the following without success :

select datum from stuff str_to_date(datum, %d/%m/%Y)

and :

select datum from stuff where convert(datetime,datum,103);

Answer

The syntax of your queries is not correct.

STR_TO_DATE() is indeed meant to convert a string to a date. As a second argument, it expects a string (not a litteral string !) that represents the format of the input string.

Given the format of your date, you may use :

SELECT STR_TO_DATE('6/2/2018 3:00:00 PM', '%e/%c/%Y %l:%i:%s %p')

Format specifier details :

• %e : Day of the month, numeric (0..31)
• %c : Month, numeric (0..12)
• %Y : Year, numeric, four digits
• %l : Hour (1..12)
• %i : Minutes, numeric (00..59)
• %s : Seconds (00..59)
• %p : AM or PM

Demo on DB Fiddle

---

If you are actually looking to convert the datatype of the column, then you would need to create a temporary column, update it with converted data, then drop the original column and rename the temporary, like :

ALTER TABLE stuff ADD (datum_new datetime);
UPDATE stuff SET datum_new = STR_TO_DATE(datum, '%e/%c/%Y %l:%i:%s %p');
ALTER TABLE stuff DROP datum;
ALTER TABLE stuff CHANGE COLUMN datum_new datum datetime;

If you are using MySQL 8.0, the last statement can be written as :

ALTER TABLE stuff RENAME COLUMN datum_new TO datum;