phpmyadmin Question posted in
The official documentation can be found here.

Change date string with "am/pm" to actual date in MySQL – Phpmyadmin

I have a column of dates written as strings with “am” and “pm” at the end, for example:

1/1/2016 12:00:00.000 AM

The type of the column is currently varchar, I want to change the type to datetime format.

Attempt 1

Changing the column type from phpmyadmin dashboard gives the following error:

Query error:
#1292 - Incorrect datetime value: '1/1/2016 12:00:00.000 AM'

Attempt 2

Considering the format of the date, I tried to use the STR_TO_DATE function. But it does not give the expected result. It instead returns the same date for each row.

SELECT STR_TO_DATE(`assembling-machine-tag`.`Time`, "%Y") from `assembling-machine-tag`

Result

enter image description here

So, how can I change the format of my column from varchar (with format = dd/mm/yy hh:mm:ss.ms AM) to datetime (with format = yyyy-mm-dd hh:mm:ss) in MySQL 7.4.1?

Tags:

2

Answers


  1. 0

    You just need to use the correct pattern:

    SELECT STR_TO_DATE('1/2/2016 01:02:03.456 AM', '%e/%c/%Y %h:%i:%s.%f %p')
-- 2016-02-01 01:02:03.456000

    In order to convert varchar data to datetime, you first need to create a temporary datetime column and update it like so:

    UPDATE t SET `datetimetemp` = STR_TO_DATE(`varchardate`, '%c/%e/%Y %h:%i:%s.%f %p')

    Once satisfied with result drop, the varchar column and rename the temporary column.

    Login or Signup to reply.
  2. 0

    I would use this version:

    SELECT STR_TO_DATE('1/1/2016 12:00:00.000 PM', '%d/%m/%Y %h:%i:%s.%f %p')
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search