mysqldatabasetimevarcharstr-to-date

How to update a MYSQL table column from Varchar data type to a time

I have a table in an MYSQL database and one of the columns (Incident_Time) has values such as 03:15 A and 12:30 P stored as varchar data type. However, I would like to change the data type from varchar to timestamp (time) so that the values can be treated as time values. For example,time as 10:37 AM.

-------------
Incident_Time
--------------
| 10:37 A    |
| 03:15 A    |
| 12:20 P    |

I tried the following code:

UPDATE incident_tab_22 
SET Incident_Time = str_to_date(Incident_Time, '%h:%i  %p');

I keep getting the following error reading:

Error 1411 (HY000): Incorrect datetime value: ‘10:37 A’ for function str_to_date

As an alternative solution, I also tried:

select *, SELECT STR_TO_DATE(Incident_Time, '%h:%i %p') ; as Time_of_Incident from incident_tab_22;

This just resulted in a column created (Time_of_Incident) with all NULL values. I would appreciate any assistance I can get with this problem. Thanks.

Solution

  • To use %p you need to add an M:

    select *, 
    STR_TO_DATE(concat(Incident_Time,'M'), '%h:%i %p') as Time_of_Incident
from incident_tab_22

    fiddle