Sorting Text Dates

10 Dec 2005 10:18 PM

Scott

I'm trying to figure out how to sort the below results in Northwind by the
month and year. Because my Date is text type, SQL is ordering it incorrect.

Anyone know a workaround?

I need a solution that will sort the m-yyyy format, but I may even need to
sort a Dec - 2005 format.

MY CODE:

SELECT CAST(DATEPART(m, Orders.OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, Orders.OrderDate) AS char(4)) AS myDate,
                      COUNT(Orders.ShipName) AS ctShipName,
COUNT(Orders.ShipCity) AS ctShipCity, COUNT(Orders.ShipCountry) AS
ctShipCountry
FROM Categories INNER JOIN
                      Products ON Categories.CategoryID =
Products.CategoryID INNER JOIN
                      Orders INNER JOIN
                      [Order Details] ON Orders.OrderID = [Order
Details].OrderID ON Products.ProductID = [Order Details].ProductID
GROUP BY CAST(DATEPART(m, Orders.OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, Orders.OrderDate) AS char(4))
ORDER BY CAST(DATEPART(m, Orders.OrderDate) AS varchar(2)) + '-' +
CAST(DATEPART(yyyy, Orders.OrderDate) AS char(4))

10 Dec 2005 11:09 PM

Erland Sommarskog

Scott (sbai***@mileslumber.com) writes:

> I'm trying to figure out how to sort the below results in Northwind by the
> month and year. Because my Date is text type, SQL is ordering it
> incorrect.
>
> Anyone know a workaround?
>
> I need a solution that will sort the m-yyyy format, but I may even need to
> sort a Dec - 2005 format.

A derived table can take you a long way:

   SELECT substring(myDate, 5, 2) + '-' +
          substring(myDate, 1, 4),
          ctShipName, ctShipCity, ctShipCountry
   FROM   (SELECT convert(char(6), Orders.OrderDate, 112) myDate,
                  COUNT(Orders.ShipName) AS ctShipName,
                  COUNT(Orders.ShipCity) AS ctShipCity,
                  COUNT(Orders.ShipCountry) AS ctShipCountry
           FROM Categories
           JOIN Products ON Categories.CategoryID = Products.CategoryID
           JOIN Orders JOIN [Order Details]
                   ON Orders.OrderID = [Order Details].OrderID
               ON Products.ProductID = [Order Details].ProductID
   GROUP BY convert(char(6), Orders.OrderDate, 112)) AS s
   ORDER BY myDate

The thing in parentheses is a derived table. You can think of it
as a temp table within the query, but it is not necessarily
materialised, and the optimizer often recast the computation order.
Here the derived table serves two things: 1) To group by year-month
2) To produce a column we can sort on, without inluding it in the
result set.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

11 Dec 2005 3:49 AM

Scott

thanks for the lesson.

Show quote

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns97291890F632Yazorman@127.0.0.1...
> Scott (sbai***@mileslumber.com) writes:
>> I'm trying to figure out how to sort the below results in Northwind by
>> the
>> month and year. Because my Date is text type, SQL is ordering it
>> incorrect.
>>
>> Anyone know a workaround?
>>
>> I need a solution that will sort the m-yyyy format, but I may even need
>> to
>> sort a Dec - 2005 format.
>
> A derived table can take you a long way:
>
>
>   SELECT substring(myDate, 5, 2) + '-' +
>          substring(myDate, 1, 4),
>          ctShipName, ctShipCity, ctShipCountry
>   FROM   (SELECT convert(char(6), Orders.OrderDate, 112) myDate,
>                  COUNT(Orders.ShipName) AS ctShipName,
>                  COUNT(Orders.ShipCity) AS ctShipCity,
>                  COUNT(Orders.ShipCountry) AS ctShipCountry
>           FROM Categories
>           JOIN Products ON Categories.CategoryID = Products.CategoryID
>           JOIN Orders JOIN [Order Details]
>                   ON Orders.OrderID = [Order Details].OrderID
>               ON Products.ProductID = [Order Details].ProductID
>   GROUP BY convert(char(6), Orders.OrderDate, 112)) AS s
>   ORDER BY myDate
>
> The thing in parentheses is a derived table. You can think of it
> as a temp table within the query, but it is not necessarily
> materialised, and the optimizer often recast the computation order.
> Here the derived table serves two things: 1) To group by year-month
> 2) To produce a column we can sort on, without inluding it in the
> result set.
>
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx