title: Converting 24hr Time to 12hr Time in SQL
body: <p>I'm fairly new to SQL and have read through many of these conversion questions posted, tried to follow syntax guidelines but am really at a loss for what to do here.</p>
  
  <p>My <code>CASE</code> statement successfully converts 24hr time to 12hr, the issue arises when I attempt to add a semicolon to the resulting time with the <code>IF</code> statement. The resulting <code>@newtime</code> variable produces a <code>NULL</code>, and I want it to only create one result, not two.</p>
  
  <p>Running Microsoft SQL Server  2000 - 8.00.760 Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2) </p>
  
  <pre><code>DECLARE @current24hrtime INT;
  SET @current24hrtime = 1522;
  DECLARE @newtime varchar(20);
  
  SELECT
      CASE
          WHEN @current24hrtime &lt; 100
              THEN CONCAT(@current24hrtime + 1200, ' AM')
          WHEN @current24hrtime &gt;= 100 AND @current24hrtime &lt;= 1159
              THEN CONCAT(@current24hrtime, ' AM')
          WHEN @current24hrtime &gt;= 1200 AND @current24hrtime &lt;= 1259
              THEN CONCAT(@current24hrtime, ' PM')
          WHEN @current24hrtime &gt;= 1300 AND @current24hrtime &lt;= 2359
              THEN CONCAT(@current24hrtime - 1200, ' PM')
  
  END;
  
  
          IF LEN(@current24hrtime) = 6
              SET @newtime = LEFT(@current24hrtime, 1) + ':' + RIGHT(@current24hrtime, 5)
          ELSE IF LEN(@current24hrtime) = 7
              SET @newtime = LEFT(@current24hrtime, 2) + ':' + RIGHT(@current24hrtime, 5)
  SELECT @newtime;
  </code></pre>