RickInWestPalmBeach RickInWestPalmBeach - 9 months ago 47
MySQL Question

Conversion of time string from AMPM to Military in MySQL

I apologize in advance for what probably seems like a duplicate question, but I have attempted every solution I could find, and nothing seems to be working.

I have a table with time stored separately as a varchar. The value of a particular record is "6:00 PM"

I have tried every function and conversion in MySQL, but cannot get this thing to convert to military time.

Any ideas?


You may want to consider storing times, not as a VARCHAR, but as a DATETIME. That way you can format it as you wish with DATE_FORMAT().

What you can do here is use STR_TO_DATE() to convert the string to a DATETIME, then use DATE_FORMAT() to format it.

-- Let's say `time` is the value "6:00 PM"
-- This should return "18:00"
SELECT DATE_FORMAT(STR_TO_DATE(`time`, '%l:%i %p'), '%k:%i') AS milTime;