SQL order by datetime formatted as dd.MM.yyyy is incorrect-CodePudding

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've 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:

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)