Date field converted to string - Does not allow Order By

I am using SQL 2008. (in asp actually).

SELECT  orderId, CONVERT(varchar, orderDate, 101) AS Date_Ordered, CONVERT(varchar, sentDate, 101) AS Date_Shipped, 
FROM orders 
GROUP BY orderId, CONVERT(varchar, o.orderDate, 101), CONVERT(varchar, o.sentDate, 101)
ORDER BY Date_Shipped

开发者_JAVA技巧OK, The reason I am using the Convert in the Select is because my Data has Dates with different times. but I want all results with the same Day. So I used the convert.

My problem is that I can not figure out how to do the Order By. I tried sorting by Date_Shipped, but its now a String so it does not sort properly.

Can anyone help?

You have SQL Server 2008 so you can use the date type

SELECT
    orderId,
    CONVERT(varchar, orderDate, 101) AS Date_Ordered,
    CONVERT(varchar, CAST(sentDate as date), 101) AS Date_Shipped
FROM orders 
GROUP BY
   orderId, CONVERT(varchar, o.orderDate, 101), CAST(sentDate as date)
ORDER BY
   CAST(sentDate as date)

I can't recall exactly if this is allowed but hopefully you get the idea: if not, use this

SELECT
    orderId,
    Date_Ordered,
    CONVERT(varchar, sentDate2, 101) AS Date_Shipped
FROM
    (
    SELECT
        orderId,
        CONVERT(varchar, orderDate, 101) AS Date_Ordered,
        CAST(sentDate as date) AS sentDate2
    FROM orders o
    GROUP BY
       orderId, CONVERT(varchar, orderDate, 101), CAST(sentDate as date)
    ) foo
ORDER BY
    sentDate2

SELECT
  orderId,
  CONVERT(varchar, orderDate, 101) AS Date_Ordered,
  CONVERT(varchar, sentDate, 101) AS Date_Shipped, 
FROM orders 
GROUP BY
  orderId,
  CONVERT(varchar, orderDate, 101),
  CONVERT(varchar, sentDate, 101)
ORDER BY MAX(sentDate)

By the way, the o aliases in your query are erroneous, because you don't have a table aliased like that.