-1
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

Vadim Kotov
  • 8,084
  • 8
  • 48
  • 62
Prasanth Kamatham
  • 1
  • What are the data types of the CalendarDate and FirstAccountOpenDate columns? Are they actual datetime fields? Also your SQL isn't valid, looks like something got cut off in copy/paste. – Jacob H May 23 '17 at 20:32
  • Does this stored procedure work? When you say debugging a store procedure, did you inherit this code or write it? If you inherited it, I would say figure out what you actually want returned and rewrite it from scratch. If you wrote it yourself, then please enlighten us on what the heck you are trying to do here. – Chris Berger May 23 '17 at 20:34
  • I finally found that the issue with a different line in the SP. I was misled by the error message that issue is with line 1. I had multiple sub queries inside the SP as I was using Sub Queries in place of temp tables in SP. Basically testers things :) – Prasanth Kamatham May 24 '17 at 19:19

1 Answers1

0

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.

Matt
  • 13,833
  • 2
  • 16
  • 28