Assign INT Variable From Select Statement in SQL

Question

Why do I get a syntax error on the following SQL statements:

DECLARE @Count90Day int;

SET @Count90Day =  SELECT COUNT(*) FROM Employee WHERE DateAdd(day,30,StartDate) BETWEEN
DATEADD(day,-10,GETDATE()) AND DATEADD(day,10,GETDATE())  AND Active ='Y' 

I am trying to assign the number of rows returned from my Select statement to the variable @Count90Day.

Answer 1

You need parentheses around the subquery:

DECLARE @Count90Day int;

SET @Count90Day =  (SELECT COUNT(*)
                    FROM Employee
                    WHERE DateAdd(day,30,StartDate) BETWEEN DATEADD(day,-10,GETDATE()) AND 
                                                            DATEADD(day,10,GETDATE())  AND
                          Active ='Y'
                   );

You can also write this without the set as:

DECLARE @Count90Day int;

SELECT @Count90Day =  COUNT(*)
FROM Employee
WHERE DateAdd(day,30,StartDate) BETWEEN DATEADD(day,-10,GETDATE()) AND DATEADD(day,10,GETDATE()) AND
      Active ='Y';

Answer 2

You can assign it within the SELECT, like so:

DECLARE @Count90Day int;

SELECT @Count90Day = COUNT(*) 
FROM Employee 
WHERE DateAdd(day,30,StartDate) BETWEEN
DATEADD(day,-10,GETDATE()) AND DATEADD(day,10,GETDATE())  AND Active ='Y' 

Answer 3

You don't assign SQL variables in a SELECT like that, you can do:

SELECT @Count90Day = COUNT(*) 
FROM Employee WHERE DateAdd(day,30,StartDate) BETWEEN
DATEADD(day,-10,GETDATE()) AND DATEADD(day,10,GETDATE())  AND Active ='Y'