title: Convert DateTime to US and European
body: <p>I need to convert a date in the format 2012-10-15 15:00:21.970</p>
  
  <p>To European: 15/10/2012 15:00:21</p>
  
  <pre><code>CONVERT(nvarchar, @DateTime, 103) + N' ' + CONVERT(nvarchar, @DateTime, 8)
  -- 15/10/2012 15:00:21
  </code></pre>
  
  <p>To US: 10/15/2012 03:00:21 PM</p>
  
  <pre><code>CONVERT(NVARCHAR, @DateTime, 101)
  -- 10/15/2012
  </code></pre>
  
  <p>I can't figure out the time portion of the US conversion. I can probably do it with DATEPART, but this function is going to format times in a select statement, so I'm trying to keep it as simple as possible. Any ideas?</p>
  
  <p>Resource: <a href="http://www.w3schools.com/sql/func_convert.asp" rel="nofollow">http://www.w3schools.com/sql/func_convert.asp</a></p>
  
  <p>Figured it out</p>
  
  <pre><code>DECLARE @DateTime datetime = '2012-10-15 15:00:21.970'
  SELECT
  CONVERT(nvarchar, @DateTime, 101) + N' ' +
  LEFT(N'0' + RIGHT(CONVERT(nvarchar, @DateTime, 109), 13), 8) + N' ' +
   CASE WHEN DATEPART(HH,@DateTime) &lt; 13
          THEN 'AM'
          ELSE 'PM'
          END
  </code></pre>