RickInWestPalmBeach RickInWestPalmBeach - 1 year ago 82
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?

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download