Converting Data Type for SQL Server View

I am trying to query a SQL view to show the transactions for the last business day (i.e. the day before, or the Friday if the day the query is ran is Monday).

My query looks like this:

DECLARE                         
    @transdate AS INT;                                              
                            
SET @transdate = DATEADD(DAY, CASE (                            
DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 WHEN 1 THEN -2 WHEN 2 THEN -3 ELSE -1 END, DATEDIFF(DAY, 0, GETDATE()))                         
                            
SELECT                          
    PolicyNumber,                           
    LossDate,                           
    SUM(Amount),                            
    (CASE
        WHEN SUM(Amount) < 0 THEN 'Decrease'                            
        WHEN SUM(Amount) >= 0 THEN 'Increase'                           
     AND) AS 'New/Decrease/Increase',
     Branch AS State,                   
     tName AS Name                      
FROM 
    ViewDataBase    
WHERE       
    CAST(CONVERT(CHAR(30), CONVERT(DATETIME, BookingDate, 105), 101) AS DATE) = CAST(CONVERT(CHAR(30), CONVERT(DATETIME, @transdate, 105), 101) AS DATE)
    AND BranchNumber IS NOT NULL 
GROUP BY        
    BookingDate,    
    BranchNumber,       
    PolicyNumber,
    LossDate,
    Name

I get the following error when I try to run this query:

Msg 257, Level 16, State 3, Server PMICDB01A, Procedure , Line 0
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query. (257)

I have tried with simply CONVERT or CAST but nothing I try seems to be the correct fix. My column BookingDate is of type Datatime2, I am not sure what type transdate is as I create it. I’m assuming my WHERE clause is the problem.

EDIT: I have also tried:

(CAST(BookingDate AS date) = CAST(@transdate AS date))

This is what I started out with:

BookingDate = @transdate

I also tried:

CONVERT(DATETIME, BookingDate) = CONVERT(DATETIME, @transdate)

and

CONVERT(DATETIME, BookingDate) = @transdate

and

BookingDate = CONVERT(datetime, @transdate)

Any suggestions?

Answer

The error you are getting is not caused by the WHERE clause. If you run just the DECLARE and the SET at the top without the SELECT, you will get the conversion error.

To correct, at the very top, first declare @transdate as a datetime:

DECLARE                         
    @transdate AS datetime;

Then try to run just the DECLARE and SET again to see if you get an error or not. If not, then you can add back the SELECT and see if it returns results. It looks like you can simplify your WHERE clause with:

WHERE CONVERT(DATE,BookingDate) = CONVERT(DATE,@transdate)
AND BranchNumber IS NOT NULL

The conversion to DATE in the WHERE clause is just in case the BookingDate includes a time.