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

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)

You could truncate the date to 12:00am instead of casting to a string:

SELECT dateadd(dd, datediff(dd, 0, create_date), 0) as 'Date'
     , COUNT(*) as 'Record Count',
  FROM the_table
 GROUP BY dateadd(dd, datediff(dd, 0, create_date), 0)
 ORDER BY dateadd(dd, datediff(dd, 0, create_date), 0) desc

You can probably substr then order by year desc, then month asc and date asc.

Does the data have to have only the two columns you specified? If not, you could select the date truncated to midnight (as user1948904 suggested) as well as the formatted-date field, and then order by the date field. Then you can ignore the date field in whatever uses the data.

Edited to correct errors in the original query, and to add the formatted-date field to the GROUP BY , which is required.

SELECT DATEADD(DAY, 0, DATEDIFF(DAY, 0, create_date)) AS raw_date,
        CONVERT(VARCHAR(10), create_date, 101) AS 'Date',
        COUNT(*) AS 'Record Count',
FROM the_table
GROUP BY DATEADD(DAY, 0, DATEDIFF(DAY, 0, create_date)),
        CONVERT(VARCHAR(10), create_date, 101)
ORDER BY raw_date DESC

I find the other answers unsuitable for my situation because I don't want an additional redundant date column or have to use a GROUP BY if I'm not really aggregating any information in the query (granted the OP's question includes count(*) - my case is identical except I'm not aggregating).

This solution uses a DATEADD() that doesn't really do anything to force SQL Server to treat it as an actual date and return the right order.

SELECT    [Date]  = CONVERT(varchar(10), t.[create_date], 101)
          [Thing] = t.[other_column]     -- that I don't want to aggregate
FROM      [db].[dbo].[mytable] t
ORDER BY  DATEADD(dd, 0, t.[create_date]) DESC

This is old I know, but I ran into this problem today with a script for work. We are running a report with a DATETIME column where we only actually ever stored the DATE, not the time, so the time shows up as 12:00AM over and over on the report which is both meaningless and redundant, so I wanted to format the value to not display the time, and I ran into this same problem because we are ordering by that same DATETIME column. Simply using FORMAT or CONVERT threw an error, and Left to truncate the value did something weird I really didn't expect - it made the DATETIME display the month as a text abbreviation - Jan 23, 2023, Feb 03, 2023, etc... Peculiar!

Anyway, I found the easiest solution was to use FORMAT on my DATETIME column instead of CONVERT, because while that formats the date so that displays the way I need it to, it seems that SQL still treats the column as a DATETIME this way and thus it sorts correctly.

How ever it is worth noting one must use FORMAT on both instances of the column - in the SELECT statement and the ORDER BY clause! Otherwise there is an error thrown if only one the SELECT statement is formatted.

Here's the an example of the query in my stored procedure with the other conditions needed for running the report removed:

select distinct property_id as 'Property ID', format(last_visit, 'yyyy-MM-dd') as 'Last Visit', street_num + ' ' + address as 'Address', city as 'City', county as 'County', state as 'State', zip_code as 'Zipcode' from property order by format(last_visit, 'yyyy-MM-dd') asc

I don't know anything about sql-server but I'll try to help. You should replace this column with one that is a Date type. I'm sure sql-server will know how to sort that correctly.

If that isn't an option for you, maybe in sql-server you can order by a function that converts the string to a date type.

But it already looks like you're using a date type here. I think you should just expand your query to include the date column in the select as the date type and sort by that column instead of the converted column.