How can I order by a date in string format properly?

Question

I have a table with the following fields in an SQL Server 2005 database:

• id, integer
• value, string
• create_date, datetime

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.

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

This query returns data looking like this:

12/20/2012 | 48155
12/19/2012 | 87561
12/18/2012 | 71467

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)

06/29/2012 | 9987
01/04/2013 | 15768
01/03/2013 | 77586
01/02/2013 | 23566

I understand why 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)

Answer 1

Thanks to Oded's suggestion I changed my order by clause and this seems to give me what I want:

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 MIN(create_date) desc

Answer 2

You can include the date as a date data type in the GROUP BY and then use it in the ORDER BY

SELECT top 100 CONVERT(varchar, create_date, 101) as 'Date', COUNT(*) as 'Record Count'
FROM constituent
GROUP BY CONVERT(varchar, create_date, 101), CONVERT(date, create_date)
ORDER BY CONVERT(date, create_date)