title: How to Sort datetime in ROW_NUMBER() OVER ( ORDER BY clause
body: <p>Hi the below query is a prototype of a bigger complex query
  The problem is that i should be able sort any column in any order (i.e. ASC and DESC) based on user input.</p>
  
  <pre><code>CREATE table #Table1(
  Name varchar(10) PRIMARY key,
  DOB DateTime,
  Rate numeric(10,2)  
  )
  
  INSERT INTO #Table1 (Name,DOB,Rate) values ('Name1','2/2/2012',10.23)
  INSERT INTO #Table1 (Name,DOB,Rate) values ('Name2','3/2/2012',120.23)
  INSERT INTO #Table1 (Name,DOB,Rate) values ('Name3','4/2/2012',110.23)
  INSERT INTO #Table1 (Name,DOB,Rate) values ('Name4','5/2/2012',140.23)
  INSERT INTO #Table1 (Name,DOB,Rate) values ('Name15','6/2/2012',130.23)
  INSERT INTO #Table1 (Name,DOB,Rate) values ('Name6','2/21/2012',1120.23)
  
  Declare @SortColumn varchar(10)
  DECLARE @SortExpression varchar (10)
  
  SET @SortColumn = 'DOB'
  SET @SortExpression = 'DESC' -- Need to sort in both ASC and DESC
  
  Select 
              Name,
              DOB,
              Rate,
              ROW_NUMBER() OVER 
                  (ORDER BY 
                      CASE WHEN @SortColumn = 'Name' then Name
                           WHEN @SortColumn = 'DOB' THEN DOB
                           WHEN @SortColumn = 'Rate' THEN Rate
                      END  +  @SortExpression 
                  ) AS RowNumber  
                  FROM 
                  #Table1
  </code></pre>