To sort date(which is in the form 'january 2001') starting from January to December

I have this query which returns a result set with Date(column) as nvarchar datatype.

SELECT DISTINCT 
    DateName( month , DateAdd( month , (CONVERT(int,DateField1)) - 1 , '2000-01-01' ) ) 
    +' '+ DateName( year , DateAdd( year , (CONVERT(int,DateField2)), '2000-01-01' ) ) AS [Date] 
FROM dbo.table1 
WHERE DateName( year , DateAdd( year , (CONVERT(int,DateField2)), '2000-01-01' ) )= 2009.

Here DateField1 and DateField2 are columns in table1.

I get the results as shown below

--------------
Date
--------------
March 2009
June 2009
August 2009
September 2009
July 2009
May 2009
November 2009
December 2009
February 2009
April 2009
January 2009
October 2009 

I would like to sort the result set starting from January 2009 to December 2009.

Help appreciated.

Thanks Sid

Answers


You can just cast the final result (I have wrapped the call in a subquery rather than order by the cast in this case)

SELECT *
FROM
(
    SELECT DISTINCT 
        CAST(
            DateName( month , DateAdd( month , (CONVERT(int,DateField1)) - 1 , '2000-01-01' ) ) 
            +' '+ DateName( year , DateAdd( year , (CONVERT(int,DateField2)), '2000-01-01' ) ) 
        AS DATETIME) AS   [Date] 
    FROM dbo.table1 
    WHERE DateName( year , DateAdd( year , (CONVERT(int,DateField2)), '2000-01-01' ) )= 2009
) AS MyDate
ORDER BY Date

SQL Server will be able to convert this appropriately: Here is the fiddle to show the cast works


Need Your Help

Haskell parse big xml file with low memory

xml memory haskell stream expat-parser

So, I've played around with several Haskell XML libraries, including hexpat and xml-enumerator. After reading the IO chapter in Real World Haskell (http://book.realworldhaskell.org/read/io.html) I ...

How AS3 class files.as work together with FLA file?

actionscript-3 flash class

I have two questions: Can i write app using just *.as files, and then compile them somehow in SWF? (i am making myself a webpage now)

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.