Order by with formatted dates, doing it right?

I wanted a mere sanity check because I have a slight feeling I am doing it all wrong. I stripped down the use case to a very simple sample.

When formatting a date I need to cast the data in the order clause to avoid sorting a varchar object. However. Initially I expected the ORDER clause to function with a basic CONVERT(DATE, OrderDate). However it throws a clear exception “Column “OrderDate” is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.”

It doesn’t throw this error if I fully replicate the formatting. To me making no sense at all first having to format the date, and then cast it back.

Works fine but feels awkward having to format in the order clause

SELECT FORMAT (OrderDate,'MMM dd yyyy') AS OrderDate, SUM(Quantity) AS Quantity 
FROM ORDERS
GROUP BY FORMAT (OrderDate, 'MMM dd yyyy')
ORDER BY CONVERT(DATE, FORMAT (OrderDate,'MMM dd yyyy')) ASC

Doesn’t work

SELECT FORMAT (OrderDate,'MMM dd yyyy') AS OrderDate, SUM(Quantity) AS Quantity 
FROM ORDERS
GROUP BY FORMAT (OrderDate, 'MMM dd yyyy')
ORDER BY CONVERT(DATE, OrderDate) ASC

Am I doing this correct though?

Answer

You can use an aggregate function in the ORDER BY clause, so you don’t need to convert the string back to a date:

SELECT FORMAT(OrderDate,'MMM dd yyyy') AS OrderDate, SUM(Quantity) AS Quantity 
FROM ORDERS
GROUP BY FORMAT(OrderDate, 'MMM dd yyyy')
ORDER BY MIN(OrderDate)

Advertisement

Answer