SQL SORT BY DATENAME does not work

Question

My query is

USE MyDB

SELECT DATENAME(DD, s.requestdate)
FROM   sample_table s
WHERE  MONTH(s.requestdate) = 12
GROUP  BY DATENAME(DD, s.requestdate)
ORDER  BY DATENAME(DD, s.requestdate) DESC 

The output I expect is to be ordered by day of month in descending order but my output is ordered such

9
8
 7....till 1 then 19, 18...till 11

What am I missing?

Answer 1

DATENAME returns a string.

Use DATEPART(DD,s.requestdate) instead as that returns an integer and will sort as you expect.