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]
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)
(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