1

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612
Lonestar101
  • 33
  • 4
  • [Tips for asking a good Structured Query Language (SQL) question](//meta.stackoverflow.com/questions/271055), #5 and #3. – Akina Jan 04 '23 at 20:16
  • 1
    You have an extra space before `%p` in the format string. – Barmar Jan 04 '23 at 20:19
  • Your alternative solution isn't valid SQL syntax, you shouldn't get any result. – Barmar Jan 04 '23 at 20:19
  • `%p` parses `AM/PM` and not `A/P` - this causes NULLs on the output. Moreover, shown value `12:20 P` claims that A/P marks are fake. So use `STR_TO_DATE(Incident_Time, '%H:%i')`. *I tried the following code* UPDATE makes no sense - this won't change the column datatype. – Akina Jan 04 '23 at 20:23
  • @Akina what do you mean fake? – ysth Jan 04 '23 at 20:29
  • @ysth I mean that it is not a part of value, it is redundand marker which simplifies the daypart distinguishing. Or backwardly, it acts as "parity bit" and allows to detect incorrect time values (see https://dbfiddle.uk/5uB1602n). – Akina Jan 04 '23 at 20:55
  • @Akina still no clue what you mean. there are 24 different hours, numbered 1 through 12 (though not occurring in that order) and either AM or PM; neither part is redundant. yes, if you specify invalid hours for the %h format, it will reject them; that's not related to %p (though %p does adjust *valid* %h hours) – ysth Jan 04 '23 at 22:56

1 Answers1

1

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

ysth
  • 96,171
  • 6
  • 121
  • 214
  • Thank you, @ysth. This provided a solution that I could work with. I appreciate your response. Thanks again. – Lonestar101 Jan 06 '23 at 01:22
  • The code produced NULL values with times that began with "00", such as 00:55 A and 00:47 A. I corrected this by using %H instead of %h. Thanks again for your assistance. – Lonestar101 Jan 06 '23 at 01:57
  • Having 00 and am/pm is weird, did it do what you expected for 00 PM? – ysth Jan 06 '23 at 02:41