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

• sql
• sql-server-2008
• tsql

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

1. You can order by the original fields (DateField2, DateField1 if I see it correctly)
2. You can form the Monthname back to a date and sort on this.
3. You can combine your calculation in the selection in the order by clause.