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


  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.

Need Your Help

Getting the ID of the element that fired an event

javascript jquery

Is there any way to get the ID of the element that fires an event?

Automatically open terminal when debugging in Xcode?

xcode macos automation terminal

When debugging an executable in Xcode, I very often have to open up a terminal window, navigate to the executable's working directory and do some work there. Is there any way to have Xcode automati...