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?
Advertisement
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)