CODEWITHSUNDEEP
sql-serversql-server-2005datetimeformatting
Scott
Scott

Reputation: 13

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 as 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!

Upvotes: 1

Views: 1027

Answers (3)

EricZ
EricZ

Reputation: 6205

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

Upvotes: 0

marc_s
marc_s

Reputation: 754268

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.

Upvotes: 1

Chandu
Chandu

Reputation: 82893

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

Upvotes: 1

Related Questions