CODEWITHSUNDEEP
sqlsql-servert-sqldate
thriluprincy
thriluprincy

Reputation: 63

How to achieve the latest date order with format MM/DD/YYYY

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

What i expected is with latest order date with formate MM/DD/YYY 

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

Upvotes: 4

Views: 155

Answers (5)

nazmul.3026
nazmul.3026

Reputation: 1008

Hope It will work 

 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

Output

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

Upvotes: 0

Chanukya
Chanukya

Reputation: 5893

i tried like this 

 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

or

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

output

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

Upvotes: 0

Santosh Jadi
Santosh Jadi

Reputation: 1527

Remove DISTINCT, and add ORDER BY DateFormate DESC in your query.

Upvotes: 2

Salman Arshad
Salman Arshad

Reputation: 272116

Since the datatype is DATE you can easily group and order the result inside the sub query. Apparently you're expecting a 1row x 1col result so you can ditch the outer FROM clause:

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

Output:

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

Upvotes: 2

Denis Rubashkin
Denis Rubashkin

Reputation: 2191

Just separate ORDER BY and DISTINCT and use Date values to sort

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

Upvotes: 0

Related Questions