SQL Server string to date conversion

I have the following query:

SELECT  o.OrderNumber,
    cast(o.DateOrdered as datetime),
    cast(oi.Category as varchar(max))

 FROM   Orders o
    LEFT OUTER JOIN OrderItems oi ON oi.OrderID = o.uid
    LEFT OUTER JOIN OrderAddresses oa ON oa.OrderID = o.uid

 WHERE O.DateOrdered LIKE '6%2011%' AND (oa.Type = '4') AND (oa.Country = 'US')

 GROUP BY cast(oi.Category as varchar(max)), cast(o.DateOrdered a开发者_JS百科s datetime), o.OrderNumber

I'm having trouble formatting the datetime as mm/dd/yyyy. No matter what I have tried I keep getting like YYYY-MM-DD 00:00:00:000. I must overlooking something simple in the syntax and was hoping someone could point me in the right direction? (SQL Server 2005)

Thanks!

---

Try CONVERT instead of CAST:

CONVERT(VARCHAR(10), o.DateOrdered , 101)

So the GROUP BY statement would look like:

GROUP BY CAST(oi.Category as varchar(MAX)), CONVERT(VARCHAR(10), o.DateOrdered , 101), o.OrderNumber 

---

CAST will only convert data types - but it doesn't give you the ability to choose any formatting.

For formatting, you'll need to use CONVERT instead (see MSDN Books Online for details):

Try

SELECT CONVERT(VARCHAR(25), GETDATE(), 101)

which should render the current date in the format you're looking for.

---

It looks like DateOrdered is VARCHAR

Please try

SELECT  o.OrderNumber,
    CONVERT(VARCHAR(25),cast(o.DateOrdered as datetime), 101),
    cast(oi.Category as varchar(max))

 FROM   Orders o
    LEFT OUTER JOIN OrderItems oi ON oi.OrderID = o.uid
    LEFT OUTER JOIN OrderAddresses oa ON oa.OrderID = o.uid

 WHERE O.DateOrdered LIKE '6%2011%' AND (oa.Type = '4') AND (oa.Country = 'US')

 GROUP BY cast(oi.Category as varchar(max)), CONVERT(VARCHAR(25),cast(o.DateOrdered as datetime), 101), o.OrderNumber