title: how to convert weird varchar "time" to real time in mysql?
body: <p>I have a time value being stored in a database as a varchar(4) and I need to convert it to real time. </p>
  
  <p>for example, if the time is "23:59" I want 11:59PM returned. </p>
  
  <p>The problem is that there is no ":" between the hours and minutes. So when I run this query</p>
  
  <pre><code>SELECT TIME_FORMAT('2359', '%h:%i');    -- 12:23, wtf??
  </code></pre>
  
  <p>However if I ran this:</p>
  
  <pre><code>SELECT TIME_FORMAT('23:59', '%h:%i');   -- returns 11:59 as expected.
  </code></pre>
  
  <p>So, to sum up:
  1. the time is stored as a varchar(4) in the database. Example: </p>
  
  <pre><code>1200, 1201, 0153, 1364, 1923
  </code></pre>
  
  <ol start="2">
  <li>I want time returned as 12 hr time with a colon in it.</li>
  </ol>
  
  <p>my brain hurts and this is prb much easier than I realize...</p>
  
  <p>like this, but for mysql
  <a href="https://stackoverflow.com/questions/1509977/convert-varchar-into-datetime-in-sql-server">Convert varchar into datetime in SQL Server</a>
  <a href="https://stackoverflow.com/questions/986058/mysql-12-hr-to-24-hr-time-conversion">mysql 12 hr to 24 hr time conversion</a></p>