SQL 如何正确按字符串格式的日期排序？

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

提问by Mansfield

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

我在 SQL Server 2005 数据库中有一个包含以下字段的表：

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

• 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 whythis 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)

我理解为什么会发生这种情况，因为我的格式化日期只是一个字符串，并且不可能期望 sql server 将它排序为字符串以外的任何内容。但我希望订购准确。我怎样才能做到这一点？（最近的一天总是最先出现）

回答by Mansfield

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

感谢 Oded 的建议，我更改了 order by 条款，这似乎给了我想要的东西：

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

回答by bobs

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

您可以将日期作为日期数据类型包含在 中GROUP BY，然后在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)

回答by Drew Leffelman

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

您可以将日期截断为 12:00 am 而不是强制转换为字符串：

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

回答by Art

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

您可能可以 substr 然后按年份降序排序，然后按月升序和日期升序排序。

回答by gmm

Does the data haveto have only the two columns you specified? If not, you could select the date truncated to midnight (as user1948904suggested) 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.

数据是否有有只有你所指定的两列？如果没有，您可以选择截断到午夜的日期（如user1948904建议的那样）以及格式化日期字段，然后按日期字段排序。然后您可以忽略任何使用数据的日期字段。

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

编辑以更正原始查询中的错误，并将格式化日期字段添加到GROUP BY，这是必需的。

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

回答by Andi Mohr

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 BYif 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).

我发现其他答案不适合我的情况，因为我不想要额外的冗余日期列，或者GROUP BY如果我没有真正汇总查询中的任何信息，则必须使用 a （授予 OP 的问题包括count(*)- 我的情况相同，除了我'不聚合）。

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.

该解决方案使用了一个DATEADD()实际上并没有做任何事情来强制 SQL Server 将其视为实际日期并返回正确顺序的 。

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

回答by Daniel Kaplan

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.

我对 sql-server 一无所知，但我会尽力提供帮助。您应该将此列替换为 Date 类型的列。我相信 sql-server 会知道如何正确排序。

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.

如果这不是您的选择，也许在 sql-server 中，您可以通过将字符串转换为日期类型的函数进行排序。

---

---

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.

但看起来您在这里使用了日期类型。我认为您应该扩展您的查询以在选择中包含日期列作为日期类型并按该列而不是转换后的列进行排序。