Sorting by converted date in a distinct select-CodePudding

In a SELECT DISTINCT I'm converting a datetime into a DD/MM/YYYY format, I then want to order by that same field. Once it's converted the original value becomes inaccessible with this error:

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

Current code looks something like this:

SELECT DISTINCT
    CONVERT(varchar, dateLink.datCreated, 103) AS LastPaymentDate
FROM 
    Person p
INNER JOIN 
    Payment dateLink ON p.paymentID = dateLink.ID
ORDER BY 
    dateLink.datCreated

The code above shows the error I've mentioned. I've tried to order by the alias which runs but because it's a varchar it sorts by the day only (I think? Doesn't sort properly either way). Is there another conversion I can do? I need the date to be in a DD/MM/YYYY format while it's a datetime in the table (DD/MM/YYYY HH:mm). The query also needs to be ordered by the date DESC.

CodePudding user response：

you can convert the datetime column into date instead of varchar then in presentation layer you can format it as you want:

SELECT DISTINCT
    CONVERT(date, dateLink.datCreated, 103) AS LastPaymentDate
FROM 
    Person p
INNER JOIN 
    Payment dateLink ON p.paymentID = dateLink.ID
ORDER BY 
    LastPaymentDate