How can I sort by date in string format?

Question

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

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

New data is constantly inserted into this table (tens of thousands of records per day), so I use the following query to compare how much data was 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 that looks like this:

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

However, when I ran this query today, I noticed that sorting did not work as expected with the multi-year cost of data in the database. Instead of the data for this year at the very top of the result set, it ended up below (entries 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, since my formatted date is just a string, and you cannot expect the sql server to sort it like anything except a string. But I would like the order to be accurate. How can i achieve this? (the last day always appears first)

Answer 1

Thanks to Oded's suggestion, I reordered the order, 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 the date data type in GROUP BY , and then use it in 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) 

Answer 3

I don't know anything about sql server, but I will try to help. You must replace this column with one that is a date type. I am sure that the sql server will know how to sort them correctly.

If this is not an option for you, perhaps in the sql server you can order a function that converts the string to a date type.

---

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

Answer 4

You can trim the date to 12:00, rather than 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 

Answer 5

You can possibly add a substrate, and then indicate the year in descending order, and then add the month and date.

Answer 6

Do the data have only two columns specified by you? If not, you can select a date truncated before midnight (as suggested by user1948904 ), as well as a formatted date field, and then sort by date field. Then you can ignore the date field in what uses the data.

Edited to correct errors in the original query and to add a formatted date field to 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 

Answer 7

I find that other answers are not suitable for my situation, because I do not want to use an extra redundant date column or should use GROUP BY if I do not collect any information in the query (if the OP question contains count(*) - my case identical, except that I do not aggregate).

This solution uses DATEADD() , which actually does nothing to force SQL Server to treat it as the actual date and return the correct 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