title: Sort row on datetime in stored procedure with temp table
body: <p>I am having a stored procedure which selects data from multiple tables(using joins) and returns it in a sorted order (asc or desc) based on the provided column. </p>
  
  <p>For generating the sorted result based on the input, I use a Temp Table.</p>
  
  <p>My problem is, when the provided column is of datetime, then the sorting is not working.
  For other columns, the procedure works as expected.</p>
  
  <p>This is the code I a using.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[usp_GetEULADetails]
  (
      @OrgKey INT
  
      ,@FilterParams FilterTypes READONLY,
      /*– Sorting Parameters */
      @SortColumn nvarchar(20) = 'Status' -- BranchCode
      ,@SortOrder nvarchar(4) = 'Asc'
  
      /*– Pagination Parameters */
      ,@PageNumber int = 0
      ,@PageSize int = 0
  
  
  )
  
  AS
  
  BEGIN
  
  SET NOCOUNT ON;
  
   DECLARE @SortCol nvarchar(20),
        @FirstRec int,
        @LastRec int,
        @TotalRows int,
        @GlobalSearch nvarchar(100),
        @EULAVersionNumber nvarchar(100)
  
  DECLARE @StatusKey INT =(SELECT dbo.GetStatusKey('ACTIVE'))
  DECLARE @RowCountSelected INT
  
  SELECT
      @EULAVersionNumber = LTRIM(RTRIM(Value))
  FROM @FilterParams
  WHERE [FieldName] = 'EULAVersions'
  
  SELECT
      @GlobalSearch = LTRIM(RTRIM(Value))
  FROM @FilterParams
  WHERE [IsGlobal] = 1
  
  SET @SortCol = LTRIM(RTRIM(@SortColumn))
  SET @FirstRec = (@PageNumber - 1) * @PageSize
  SET @LastRec = (@PageNumber * @PageSize + 1)
  
  CREATE TABLE #ResultTempTable (
  ROWNUM int
  ,ORGKEY int
  ,EULAVersionKey int
  ,EULAVersionNumber varchar(25)
  ,EULAVersionUpdateDate datetime
  ,STATUSKEY tinyint
  ,DocumentFileName varchar(100)
  ,DocumentGUID varchar(100)
  ,DocumentTypeName varchar(50)
  ,DocumentDescription varchar(128)
  ,ContactKey int
  ,ContactFirstName varchar(50)
  ,ContactMiddleName varchar(50)
  ,ContactLastName varchar(50)
  ,StatusName varchar(25)
  )
  insert into #ResultTempTable (
          ROWNUM
          ,ORGKEY 
          ,EULAVersionKey 
          ,EULAVersionNumber 
          ,EULAVersionUpdateDate 
          ,STATUSKEY 
          ,DocumentFileName 
          ,DocumentGUID 
          ,DocumentTypeName 
          ,DocumentDescription
          ,ContactKey 
          ,ContactFirstName 
          ,ContactMiddleName
          ,ContactLastName
          ,StatusName 
  )
  (
      SELECT 
          ROW_NUMBER() OVER (ORDER BY
          CASE
          WHEN (@SortCol = 'EULAVersionNumber' AND
              @SortOrder = 'ASC') THEN V.EULAVersionNumber
          END ASC,
          CASE
          WHEN (@SortCol = 'EULAVersionNumber' AND
              @SortOrder = 'DESC') THEN V.EULAVersionNumber
          END DESC,
          CASE
          WHEN (@SortCol = 'DocumentFileName' AND
              @SortOrder = 'ASC') THEN V.DocumentFileName
          END ASC,
          CASE
          WHEN (@SortCol = 'DocumentFileName' AND
              @SortOrder = 'DESC') THEN V.DocumentFileName
          END DESC,
          CASE
              WHEN (@SortCol = 'EULAVersionUpdateDate' AND
                @SortOrder = 'ASC') THEN convert(date, V.EULAVersionUpdateDate,103)
            END ASC,
          CASE
              WHEN (@SortCol = 'EULAVersionUpdateDate' AND
                @SortOrder = 'DESC') THEN convert(date, V.EULAVersionUpdateDate,103)
            END DESC,
  
          CASE
          WHEN (@SortCol = 'ContactFirstName' AND
              @SortOrder = 'ASC') THEN V.ContactFirstName
          END ASC,
          CASE
          WHEN (@SortCol = 'ContactFirstName' AND
              @SortOrder = 'DESC') THEN V.ContactFirstName
          END DESC,
  
          CASE
          WHEN (@SortCol = 'StatusKey' AND
              @SortOrder = 'ASC') THEN V.StatusKey
          END ASC,
          CASE
          WHEN (@SortCol = 'StatusKey' AND
              @SortOrder = 'DESC') THEN V.StatusKey
          END DESC
  
    ) AS ROWNUM
      ,[ORGKEY]
      ,[EULAVersionKey]
      ,[EULAVersionNumber]
      ,[EULAVersionUpdateDate]
      ,[STATUSKEY]
      ,[DocumentFileName]
      ,[DocumentGUID]
      ,[DocumentTypeName]
      ,[DocumentDescription]
      ,[ContactKey]
      ,[ContactFirstName]
      ,[ContactMiddleName]
      ,[ContactLastName]
      ,[StatusName]
  
  FROM (
          SELECT
              EUV.[ORGKEY]
              ,EUV.[EULAVersionKey]
              ,EUV.[EULAVersionNumber]
              ,EUV.[EULAVersionUpdateDate]
              ,EUV.[STATUSKEY]
              ,DOC.[DocumentFileName]
              ,DOC.[DocumentGUID]
              ,DOC.[DocumentDescription]
              ,DOCTYP.[DocumentTypeName]
              ,CN.[ContactKey]
              ,CN.[ContactFirstName]
              ,CN.[ContactMiddleName]
              ,CN.[ContactLastName]
              ,ST.[StatusName]
  
          FROM [dbo].[EULAVersions] EUV
              JOIN [dbo].[Documents] DOC
                  ON DOC.[DocumentKey]  =EUV.[DocumentKey]
              JOIN[dbo].[DocumentTypes] DOCTYP
                  ON DOCTYP.[DocumentTypeKey]=EUV.[DocumentTypeKey]
              JOIN [dbo].[UserContacts] UC
                  ON UC.[UserKey]=EUv.[EULAVersionUpdatedBy]
              JOIN [dbo].[Contacts] CN
                  ON CN.[ContactKey]=UC.[ContactKey]
              JOIN [dbo].[StatusTypes] ST
                  ON ST.[StatusKey]=EUV.[StatusKey]
  
          WHERE 
              EUV.[ORGKEY]=@OrgKey --AND EUV.[StatusKey]=@StatusKey
              AND EUV.[EULAVersionNumber] LIKE '%' + ISNULL(@EULAVersionNumber, EULAVersionNumber) + '%'
              AND EUV.[EULAVersionNumber] LIKE '%' + ISNULL(@GlobalSearch, EULAVersionNumber) + '%') AS V
  
      )
  IF (@PageNumber &lt;&gt; 0)
      BEGIN
          SELECT
              @TotalRows = COUNT(1)
          FROM #ResultTempTable AS CPC
          WHERE ROWNUM BETWEEN @FirstRec AND CASE
              WHEN @PageNumber = 0 THEN @TotalRows
              ELSE @LastRec
          END
  
          SELECT 
               [ORGKEY]
              ,[EULAVersionKey]
              ,[EULAVersionNumber]
              ,[EULAVersionUpdateDate]
              ,[STATUSKEY]
              ,[DocumentFileName]
              ,[DocumentGUID]
              ,[DocumentDescription]
              ,[DocumentTypeName]
              ,[ContactFirstName]
              ,[ContactMiddleName]
              ,[ContactLastName]
              ,[StatusName]
  
          FROM #ResultTempTable AS CPC
  
          WHERE ROWNUM BETWEEN @FirstRec AND CASE
            WHEN @PageNumber = 0 THEN @TotalRows
            ELSE @LastRec
          END
          ORDER BY ROWNUM ASC
        END
        ELSE
        BEGIN
          SELECT
            @TotalRows = COUNT(1)
          FROM #ResultTempTable AS CPC
  
  
          SELECT 
               [ORGKEY]
              ,[EULAVersionKey]
              ,[EULAVersionNumber]
              ,[EULAVersionUpdateDate]
              ,[STATUSKEY]
              ,[DocumentFileName]
              ,[DocumentGUID]
              ,[DocumentDescription]
              ,[DocumentTypeName]
              ,[ContactFirstName]
              ,[ContactMiddleName]
              ,[ContactLastName]
              ,[StatusName]
  
          FROM #ResultTempTable AS CPC
  
          ORDER BY ROWNUM ASC
  END
  
   SELECT @TotalRows AS TotalCount
     IF OBJECT_ID('tempdb..#ResultTempTable') IS NOT NULL
      DROP TABLE #ResultTempTable
  END
  </code></pre>
  
  <p>--FilterTypes Details</p>
  
  <pre><code>CREATE TYPE [dbo].[FilterTypes] AS TABLE(
  [FieldName] [varchar](100) NOT NULL,
  [Value] [varchar](800) NOT NULL,
  [IsGlobal] [bit] NULL DEFAULT ((0))
  )
  </code></pre>
  
  <p>Sample Exec</p>
  
  <pre><code>declare @FilterParams AS FilterTypes;
  insert into @FilterParams values('EULAVersions','',1)
  
  exec [usp_GetEULADetails] 2,@FilterParams,'EULAVersionUpdateDate','desc',0,0
  </code></pre>
  
  <p>Obtained Result</p>
  
  <p><a href="https://i.stack.imgur.com/GsTqw.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/GsTqw.png" alt="Unsorted Output for datetime"></a></p>
  
  <p>Expected Result</p>
  
  <pre><code>declare @FilterParams AS FilterTypes;
  insert into @FilterParams values('EULAVersions','',1)
  
  exec [usp_GetEULADetails] 2,@FilterParams,'ContactFirstName','desc',0,0
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/yUWhu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yUWhu.png" alt="Sorted Output as expected"></a></p>
  
  <p>The issue I face is how to properly sort the data when data type is of datetime ?</p>
  
  <p>Thanks for the help in advance....</p>