SQL How to order by date after a convertion to char?

Question

I have a datetime column from which I want to show the dates with format "dd/mm/yyyy"

I tried with

Select distinct CONVERT(VARCHAR(10), [DateAndTime], 103) as [DateAndTime]
from dbo.Prod
order by dbo.Prod.[DateAndTime]

but it gives me the error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

If i exclude the last line it gives me a result like this

DateAndTime
03/03/2023
16/02/2023
17/02/2023
21/02/2023

What can I do to have this result ordered by the original column date format?

Answer 1

You can use group by instead of DISTINCT to do it:

Select CONVERT(VARCHAR(10), [DateAndTime], 103) as [DateAndTime]
from dbo.Prod
group by CONVERT(VARCHAR(10), [DateAndTime], 103), CAST(DateAndTime AS DATE)
order by CAST(DateAndTime AS DATE)

As other say though, this is usually better to do on frontend side of things