0

How can I convert the date 2009-01-27 11:47:00.000 to 24 Jan 2009 but also be able to retain date datatype to be able to sort as date not alphabetically?

I.e. date sort order

24 Jan 2009
03 Feb 2009

NOT 

03 Feb 2009
24 Jan 2009

Thank you.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
HL8
  • 1,369
  • 17
  • 35
  • 49

2 Answers2

2

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
codechurn
  • 3,870
  • 4
  • 45
  • 65
  • Thank for your response. How about if I don't want to order by the time stamp, as I to order by date then by customer_no? – HL8 Feb 08 '12 at 02:23
  • See this [thread](http://stackoverflow.com/questions/1177449/best-approach-to-remove-time-part-of-datetime-in-sql-server). If you are using SQL Server 2008, you would do **Order by cast(ShipDate as DATE) Desc** or if using and older SQL Server version **Order by DATEADD(dd, DATEDIFF(dd, 0, ShipDate), 0)** – codechurn Feb 08 '12 at 02:45
2
SELECT PrettyDate = CONVERT(CHAR(11), ShipDate, 106)
  FROM dbo.Orders
  ORDER BY CONVERT(DATE, ShipDate), customer_no;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490