asked on

SQL: Order By case statement error message

Error: Conversion failed when converting date and/or time from character string.

When I run the stored procedure I get the above error ONLY WHEN @pSortBy = 'Customer Name'.

I have a stored procedure which needs to order by @pSortBy passed to the stored procedure.
If @pSortBy = 'Customer Name', then order by CA.Name1 which is a VARCHAR
If @pSortBy = 'Account Number' then order by CA.AccountNumber which is an INT
If @pSortBy = 'Projected Funding Date' then order by the calculated field below which is a DATETIME

CREATE PROCEDURE [pReport_ProjectedFunding]                        
@pTodayDate Datetime,
@pPayeeTypeId INT,
@pSpecificPayee VARCHAR(8000),                        
@pDisbursementDay INT,    
@pSortBy VARCHAR(50)                   
  
AS                        
BEGIN

SELECT *
FROM ...
WHERE ...


ORDER BY   
 (CASE @SortBy  
  WHEN 'Customer Name' THEN CA.[Name1]
  WHEN 'Account Number' THEN CA.AccountNumber 
  WHEN 'Projected Funding Date'   
   THEN (CASE WHEN CFD.Data IS NOT NULL   
       THEN (CASE WHEN dbo.GetWeekDay(CFD.Data) = DATEPART(dw, #tmpAnticipatedReleaseDate.AnticipatedReleaseDate)  
            THEN #tmpAnticipatedReleaseDate.AnticipatedReleaseDate  
            ELSE DATEADD(dd,   
            (CASE DATEPART(dw, #tmpAnticipatedReleaseDate.AnticipatedReleaseDate) - dbo.GetWeekDay(CFD.Data)  
             WHEN -3 THEN 3  
             WHEN -2 THEN 2  
             WHEN -1 THEN 1  
             WHEN  1 THEN 6  
             WHEN  2 THEN 5  
             WHEN  3 THEN 4  
            END)  
            , #tmpAnticipatedReleaseDate.AnticipatedReleaseDate)  
          END)     
       ELSE #tmpAnticipatedReleaseDate.AnticipatedReleaseDate  
      END)  
  ELSE CA.AccountNumber    
 END) 
 
 END

Open in new window

PortletPaul

If you are ONLY getting that error for @pSortBy = 'Customer Name'

then the code snippet you have given us does not reveal the problem.

pzozulka

ASKER

Another developer here suggested to cast all fields after the THEN clause as varchar. This fixed the problem with the error message, however, the order by stopped working correctly as it wasn't sorting the date field correctly. They said something about the order by clause expecting a results of the case statemt to be a single data type.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

You have to convert the int and datetime values to varchar values that will sort properly:

ORDER BY
CASE @SortBy
WHEN 'Customer Name' THEN CA.[Name1]
WHEN 'Account Number' THEN RIGHT(REPLICATE('0', 10) + CAST(CA.AccountNumber AS varchar(10)), 10)
WHEN 'Projected Funding Date'
THEN CONVERT(varchar(8), (CASE WHEN CFD.Data IS NOT NULL
THEN (CASE WHEN dbo.GetWeekDay(CFD.Data) = DATEPART(dw, #tmpAnticipatedReleaseDate.AnticipatedReleaseDate)
THEN #tmpAnticipatedReleaseDate.AnticipatedReleaseDate
ELSE DATEADD(dd,
(CASE DATEPART(dw, #tmpAnticipatedReleaseDate.AnticipatedReleaseDate) - dbo.GetWeekDay(CFD.Data)
WHEN -3 THEN 3
WHEN -2 THEN 2
WHEN -1 THEN 1
WHEN 1 THEN 6
WHEN 2 THEN 5
WHEN 3 THEN 4
END)
, #tmpAnticipatedReleaseDate.AnticipatedReleaseDate)
END)
ELSE #tmpAnticipatedReleaseDate.AnticipatedReleaseDate
END), 112)
ELSE CA.AccountNumber
END

pzozulka

ASKER

Scott, as mentioned, we tried this and for some reason it is not sorting the calculated datetime correctly.

PortletPaul: Your solution worked great. Thanks.

Scott Pletcher

>> Scott, as mentioned, we tried this and for some reason it is not sorting the calculated datetime correctly. <<

That's because you didn't format it correctly for sorting. The format I used, YYYYMMDD, will sort correctly. If you need to include the time, we could of done that as well.