title: SQL - Converting 24-hour ("military") time (2145) to "AM/PM time" (9:45 pm)
body: <p>I have 2 fields I'm working with that are stored as smallint military structured times. <br> <b>Edit</b> I'm running on IBM Informix Dynamic Server Version 10.00.FC9</p>
  
  <p>beg_tm and end_tm</p>
  
  <p>Sample values<br></p>
  
  <pre><code>beg_tm   545
  end_tm   815
  
  beg_tm   1245
  end_tm   1330
  </code></pre>
  
  <p>Sample output<br></p>
  
  <pre><code>beg_tm   5:45 am
  end_tm   8:15 am
  
  beg_tm   12:45 pm
  end_tm   1:30 pm
  </code></pre>
  
  <p>I had this working in Perl, but I'm looking for a way to do it with SQL and case statements.</p>
  
  <p>Is this even possible?
  <hr>
  <b>EDIT</b></p>
  
  <p>Essentially, this formatting has to be used in an ACE report.  I couldn't find a way to format it within the output section using simple blocks of</p>
  
  <pre><code>if(beg_tm&gt;=1300) then
  beg_tm = vbeg_tm - 1200
  </code></pre>
  
  <p>Where vbeg_tm is a declared char(4) variable
  <hr>
  <b>EDIT</b>
  This works for hours >=1300 (EXCEPT FOR 2230 !!) <br></p>
  
  <pre><code>select substr((beg_tm-1200),0,1)||":"||substr((beg_tm-1200),2,2) from mtg_rec where beg_tm&gt;=1300;
  </code></pre>
  
  <p>This works for hours &lt; 1200 (sometimes.... 10:40 is failing)<br></p>
  
  <pre><code>select substr((mtg_rec.beg_tm),0,(length(cast(beg_tm as varchar(4)))-2))||":"||(substr((mtg_rec.beg_tm),2,2))||" am" beg_tm from mtg_rec where mtg_no = 1;
  </code></pre>
  
  <p><hr>
  <b>EDIT</B><BR>
  Variation of casting syntax used in Jonathan Leffler's expression approach<br></p>
  
  <pre><code>SELECT  beg_tm,
          cast((MOD(beg_tm/100 + 11, 12) + 1) as VARCHAR(2)) || ':' ||
          SUBSTRING(cast((MOD(beg_tm, 100) + 100) as CHAR(3)) FROM 2) ||
          SUBSTRING(' am pm' FROM (MOD(cast((beg_tm/1200) as INT), 2) * 3) + 1 FOR 3),
          end_tm,
          cast((MOD(end_tm/100 + 11, 12) + 1) as VARCHAR(2)) || ':' ||
          SUBSTRING(cast((MOD(end_tm, 100) + 100) as CHAR(3)) FROM 2) ||
          SUBSTRING(' am pm' FROM (MOD(cast((end_tm/1200) as INT), 2) * 3) + 1 FOR 3)
        FROM mtg_rec
        where mtg_no = 39;
  </code></pre>