I am debugging a stored procedure and trying to understand a Convert statement with Case

Question

SELECT AcquistionDate = CONVERT(NVARCHAR,
                        CASE 
                           WHEN D.CalendarDate NOT IN ('01/01/1900','12/31/9999') 
                              THEN D.CalendarDate 
                           WHEN ac.FirstAccountOpenDate NOT IN ('01/01/1900', '12/31/9999')  
                              THEN ac.FirstAccountOpenDate 
                        END, 126) + 'Z 
from TABLE;

I am getting an error

Msg 8114, level 16, state 5, line 1
Error converting data type varchar to bigint

Answer 1

The CONVERT(NVARCHAR....) takes a date and converts it to NVARCHAR in format 126 or yyyy-mm-ddThh:mi:ss.mmm

But then you have +'Z from table I will assume that is a copy paste mistake and that the single quote is not supposed to be there and it is actually + Z from Table in which case I will assume Z is a bigint. Which would mean you are trying to add a NVARCHAR to a BIGINT, which means the NVARCHAR is first tried to be converted to BIGINT which will fail because it is a string representation of a date and not numeric.

The other possibly without knowing more about your code or your DB structure is that one of the date fields in the CASE expression is actually a BIGINT and when you are comparing it to a date representation of a string the conversation fails there.

But I do agree with Chris Berger's comment that this looks like it should be written very differently.