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

Go To StackoverFlow.com

1

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

2012-04-03 20:25
by user1176058
What RDBMS are you using - Justin Pihony 2012-04-03 20:29
sql server 200 - user1176058 2012-04-03 20:55


2

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

2012-04-03 21:03
by Justin Pihony
Thanks Justin. Its Clear - user1176058 2012-04-03 21:19


1

  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.
2012-04-03 20:31
by Angelo Fuchs