如何使用格式 MM/DD/YYYY 实现最新的日期顺序

Question

IF OBJECT_ID('TEMPDB..#CommaDate') IS NOT NULL
DROP TABLE #CommaDate
CREATE TABLE #CommaDate
(
DateFormate DATE
)
INSERT INTO #CommaDate VALUES('2018-02-24'),('2018-01-10'),('2017-05-23'),('2017-04-06')
SELECT DISTINCT STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR(20),DateFormate,101) 
FROM #CommaDate 
ORDER BY ',' + CONVERT(VARCHAR(20),DateFormate,101) DESC 
FOR XML PATH('')), 1,1, '')
FROM #CommaDate

我所期望的是甲酸月/日/年的最新订单日期

02/24/2018,01/10/2018,05/23/2017,04/06/2017

Answer 1

由于数据类型是DATE因此您可以轻松地在子查询中对结果进行分组和排序。显然，您期望得到 1row x 1col 结果，因此您可以放弃外部FROM子句：

SELECT STUFF((SELECT ',' + CONVERT(VARCHAR(20), DateFormate, 101)
FROM #CommaDate
GROUP BY DateFormate
ORDER BY DateFormate DESC
FOR XML PATH('')
), 1, 1, '')

输出：

(No column name)
02/24/2018,01/10/2018,05/23/2017,04/06/2017

Answer 2

删除DISTINCT，并在查询中添加ORDER BY DateFormate DESC。

Answer 3

我试过这样

IF OBJECT_ID('TEMPDB..#CommaDate') IS NOT NULL
DROP TABLE #CommaDate
CREATE TABLE #CommaDate
(
DateFormate DATE
)
INSERT INTO #CommaDate VALUES('2018-02-24'),('2018-01-10'),('2017-05-23'),('2017-04-06')
SELECT DISTINCT STUFF((SELECT  ',' + CONVERT(VARCHAR(20),DateFormate,101) 
FROM #CommaDate 
ORDER BY concat(',' , year(CONVERT(VARCHAR(20),DateFormate,101))) desc  
FOR XML PATH('')), 1,1, '')
FROM #CommaDate

或

SELECT  distinct STUFF((SELECT  ',' + CONVERT(VARCHAR(20),DateFormate,101) 
FROM #CommaDate 
-- ORDER BY concat(',' , year(CONVERT(VARCHAR(20),DateFormate,101))) desc  
FOR XML PATH('')), 1,1, '')
FROM #CommaDate

输出

02/24/2018,01/10/2018,05/23/2017,04/06/2017

Answer 4

只需分隔ORDER BY和DISTINCT并使用日期值进行排序

IF OBJECT_ID('TEMPDB..#CommaDate') IS NOT NULL
DROP TABLE #CommaDate
CREATE TABLE #CommaDate
(
DateFormate DATE
)
INSERT INTO #CommaDate VALUES('2018-02-24'),('2018-01-10'),('2017-05-23'),('2017-04-06')
SELECT STUFF((SELECT ',' + CONVERT(VARCHAR(20),d.DateFormate,101) 
FROM ( 
SELECT DISTINCT DateFormate 
FROM #CommaDate
) d
ORDER BY d.DateFormate DESC 
FOR XML PATH('')), 1,1, '')
FROM #CommaDate

Answer 5

希望它会起作用

IF OBJECT_ID('TEMPDB..#CommaDate') IS NOT NULL
DROP TABLE #CommaDate
CREATE TABLE #CommaDate
(
DateFormate DATE
)
INSERT INTO #CommaDate VALUES ('2018-02-24'),('2018-01-10'),('2017-05-23'),('2017-04-06')

SELECT DISTINCT STUFF(
(
select  ', ' +CONVERT(VARCHAR(20),convert(datetime,DateFormate,101),101)  from #CommaDate order by  DateFormate desc
FOR XML PATH('')
), 1,1, '')
FROM #CommaDate

输出

02/24/2018, 01/10/2018, 05/23/2017, 04/06/2017