eviljack eviljack - 1 year ago 87
MySQL Question

how to convert weird varchar "time" to real time in mysql?


I have a time value being stored in a database as a varchar(4) and I need to convert it to real time.

for example, if the time is "23:59" I want 11:59PM returned.

The problem is that there is no ":" between the hours and minutes. So when I run this query
SELECT TIME_FORMAT('2359', '%h:%i'); -- 12:23, wtf??

However if I ran this:
SELECT TIME_FORMAT('23:59', '%h:%i'); -- returns 11:59 as expected.

So, to sum up:
1. the time is stored as a varchar(4) in the database. Example:
1200, 1201, 0153, 1364, 1923
2. I want time returned as 12 hr time with a colon in it.

my brain hurts and this is prb much easier than I realize...

like this, but for mysql
Convert varchar into datetime in SQL Server
mysql 12 hr to 24 hr time conversion

Answer Source

nevermind, this works:


Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download