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