As i said in the title i have problems sorting my data in the correct order. I have tried a few different combinations but they all return the data in the wrong manner. The date column is in datetime format A few codes that i have tried :
SELECT CONVERT(VARCHAR(10), recievedDate, 104) as day
FROM table
GROUP BY CONVERT(VARCHAR(10), recievedDate, 104)
ORDER BY CONVERT(VARCHAR(10), recievedDate, 104)
Returns data as :
01.02.2021
01.03.2021
01.04.2021
02.02.2021
02.03.2021
...
I'v tried :
SELECT FORMAT(CONVERT(DATETIME, CONVERT(VARCHAR(10), recievedDate, 121)),'dd.MM.yyyy')
FROM table
GROUP BY FORMAT(CONVERT(DATETIME, CONVERT(VARCHAR(10), recievedDate, 121)),'dd.MM.yyyy')
ORDER BY FORMAT(CONVERT(DATETIME, CONVERT(VARCHAR(10), recievedDate, 121)),'dd.MM.yyyy')
Which returns same results
But what i want is :
01.01.2021
02.01.2021
03.01.2021
01.02.2021
And so on...i really don't know why it groups up all the months then orders them by the day
CodePudding user response:
Group the datetime
by converting to date
and format the date after grouping:
SELECT CONVERT(VARCHAR(10), CONVERT(date, recievedDate), 104)
FROM t
GROUP BY CONVERT(date, recievedDate)
ORDER BY CONVERT(date, recievedDate)