SQL Server 2008 - Date format still retain date datatype

You can convert it to a string in your select, but still order by on the actual column:

Select CONVERT(varchar(11), Orders.ShipDate, 6) As FormatedDate
FROM Orders
Order by ShipDate Desc

SELECT PrettyDate = CONVERT(CHAR(11), ShipDate, 106)
  FROM dbo.Orders
  ORDER BY CONVERT(DATE, ShipDate), customer_no;