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