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?

Why are you converting the date and time value to a `varchar` in the first place? — Thom A, Mar 06 '23 at 09:19
Also, as an FYI, [3+ part naming on Columns will be Deprecated](https://wp.larnu.uk/3-part-naming-on-columns-will-be-deprecated/) and should be avoided. You are far better off aliasing your objects in the `FROM` and using those to qualify your columns. — Thom A, Mar 06 '23 at 09:20
I'm converting it into varchar because i couldn't find another way to have the result date with the format "dd/mm/yyyy" — dNaver, Mar 06 '23 at 09:26
The format the date is presented in is a job for your presentation layer, not the SQL layer, @dNaver . — Thom A, Mar 06 '23 at 09:28

score 1 · Accepted Answer · answered Mar 06 '23 at 09:56

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

score 0 · Answer 2 · answered Mar 06 '23 at 10:36

You need to order by the column name in the SELECT, rather than a column from one of the tables, as once you have DISTINCT you effectively have a GROUP BY on all columns. So you need to drop the table reference in the ORDER BY.

SELECT DISTINCT
  CONVERT(VARCHAR(10), p.DateAndTime, 103) as DateAndTime
FROM dbo.Prod p
ORDER BY
  DateAndTime;

Note however that this is inefficient, because you are not able to use indexes. Furthermore, DISTINCT is usually a code-smell, as it often indicates poor data.

You can group by a conversion to date instead

SELECT DISTINCT
  CONVERT(date, p.DateAndTime) as DateAndTime
FROM dbo.Prod p
ORDER BY
  DateAndTime;

Or if you use GROUP BY then you can place it in a CROSS APPLY to avoid having to specify it multiple times.

SELECT
  v.DateAndTime  
FROM dbo.Prod p
CROSS APPLY (
    SELECT CONVERT(date, p.DateAndTime) as DateAndTime
) v
GROUP BY
  v.DateAndTime
ORDER BY
  v.DateAndTime;

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

2 Answers2