title: How can I order by a date in string format properly?
body: <p>I have a table with the following fields in an SQL Server 2005 database:</p>
  
  <ul>
  <li><code>id, integer</code></li>
  <li><code>value, string</code></li>
  <li><code>create_date, datetime</code></li>
  </ul>
  
  <p>New data is constantly being inserted into this table (tens of thousands of records per day) so I use the following query to compare how much data has been inserted on different days. </p>
  
  <pre><code>SELECT CONVERT(varchar(10), create_date, 101) as 'Date', COUNT(*) as 'Record Count',
  FROM the_table
  GROUP BY CONVERT(varchar(10), create_date, 101)
  ORDER BY 'Date' desc
  </code></pre>
  
  <p>This query returns data looking like this:</p>
  
  <pre><code>12/20/2012 | 48155
  12/19/2012 | 87561
  12/18/2012 | 71467
  </code></pre>
  
  <p>However, when running this query today, I noticed the sorting did not work as expected with multiple years worth of data in the database. Instead of the data for this year being at the very top of the result set, it ended up at the bottom (records omitted for clarity)</p>
  
  <pre><code>06/29/2012 | 9987
  01/04/2013 | 15768
  01/03/2013 | 77586
  01/02/2013 | 23566
  </code></pre>
  
  <p>I understand <strong>why</strong> this is happening, as my formatted date is simply a string, and sql server can't possibly be expected to sort it as anything but a string. But I would like the ordering to be accurate. How can I achieve this? (the most recent day always appearing first)</p>