SQL中格式为dd.MM.yyyy的datetime字段ORDER BY排序错误问题

问题原因 #

你当前的排序逻辑是基于格式化后的字符串进行的，dd.MM.yyyy格式的字符串排序规则是从左到右逐字符比较，会优先比较日的数值，其次是月，最后是年，自然就会出现所有1号的日期排在一起、再排2号的结果，和你想要的按时间先后排序的逻辑不符。

正确实现方案 #

你只需要保证排序时基于原始日期类型的数值，而不是格式化后的字符串即可，参考写法：

SELECT CONVERT(VARCHAR(10), CAST(recievedDate AS DATE), 104) AS day
FROM [table]
GROUP BY CAST(recievedDate AS DATE)
ORDER BY CAST(recievedDate AS DATE)

写法说明 #

• 先将datetime类型的recievedDate用CAST(xxx AS DATE)截断为纯日期类型，分组时直接用这个日期类型值分组，避免重复的格式化操作
• 排序时直接基于日期类型的数值排序，自然会按照年→月→日的时间先后顺序排列
• 最后仅在返回展示时做字符串格式化，不影响排序逻辑

如果你的数据库不支持直接CAST AS DATE（比如低版本SQL Server），也可以用下述兼容写法：

SELECT CONVERT(VARCHAR(10), recievedDate, 104) AS day
FROM [table]
GROUP BY CONVERT(VARCHAR(10), recievedDate, 104), CONVERT(VARCHAR(10), recievedDate, 112)
ORDER BY CONVERT(VARCHAR(10), recievedDate, 112)

这里用到的112格式是yyyyMMdd的数字字符串，排序时会严格按照年、月、日的顺序排列，也能得到你要的结果。

内容的提问来源于stack exchange，提问作者BtecK