Msg 206, Level 16, State 2, Line 5 Operand type clash: int is incompatible with date

Question

I am getting error ,
Msg 206, Level 16, State 2, Line 5
Operand type clash: int is incompatible with date

when i execute below query in my data,i am using sql server 2014

		 Declare @StartDate date='2022-02-01'
 Declare @EndDate date='2022-02-05'

 ;WITH CTE AS
 (
 SELECT Contno,C.Entrydate,0 AS Opening_Weight,CASE WHEN C.Entrydate BETWEEN @StartDate AND @EndDate THEN ConWeight ELSE 0 END AS IN_Weight,
        SUM(CASE WHEN I.Entrydate BETWEEN @StartDate AND @EndDate THEN IWeight ELSE 0 END)AS Issue_Weight
 FROM Containerno C LEFT JOIN ConIssuance I ON C.CID=I.CID
 WHERE (C.Entrydate < @StartDate AND I.Entrydate BETWEEN @StartDate AND @EndDate)OR C.Entrydate BETWEEN @StartDate AND @EndDate
 GROUP BY Contno,C.Entrydate,ConWeight
 UNION
 SELECT Contno,C.Entrydate,ConWeight-SUM(ISNULL(IWeight,0)) AS Opening_Weight,0 AS IN_Weight,0 AS Issue_Weight
 FROM Containerno C LEFT JOIN ConIssuance I ON C.CID=I.CID AND ISNULL(I.Entrydate,0)< @StartDate
 WHERE C.Entrydate < @StartDate 
 GROUP BY Contno,C.Entrydate,ConWeight
 )
 SELECT Contno AS Barcode,Entrydate AS IN_Date,SUM(Opening_Weight)AS Opening_Weight,SUM(IN_Weight)AS IN_Weight,SUM(Issue_Weight)AS Issue_Weight,
        CASE WHEN SUM(Opening_Weight) =0 THEN SUM(IN_Weight)-SUM(Issue_Weight) ELSE SUM(Opening_Weight)-SUM(Issue_Weight) END AS Closing_Weight
 FROM CTE
 GROUP BY Contno,Entrydate

Answer 1

Try replacing ISNULL(I.Entrydate, 0) with ISNULL(I.Entrydate, ' ' ).

Answer 2

Your code has a line that reads

FROM Containerno C LEFT JOIN ConIssuance I ON C.CID=I.CID AND ISNULL(I.Entrydate,0)< @StartDate

If I.Entrydate is NULL you are setting the value to 0. That works if I.Entrydate is a datetime, because with datetime columns 0 was treated as '1900-01-01 00:00:00.000'. But with the newer date date datatypes like date and datetime2 you can't do that. If you want it to be January 1, 1900, then do

FROM Containerno C LEFT JOIN ConIssuance I ON C.CID=I.CID AND ISNULL(I.Entrydate,'19000101')< @StartDate

Tom