[SOLVED] "Date is Incompatible with int" while using sub-query

"Date is Incompatible with int" while using sub-query

I'm a newbie to SQL still. I'm trying to display all "person" records where their birthdate is before 1955, using a sub-query to pull all employee birthdates from before that year:

SELECT 
    PP.FirstName, PP.LastName
FROM 
    Person.Person PP
INNER JOIN
    HumanResources.Employee HE ON PP.BusinessEntityID = HE.BusinessEntityID
WHERE 
    BirthDate = (SELECT YEAR(BirthDate)
                 FROM HumanResources.Employee
                 WHERE YEAR(BirthDate) < 1955)

I've tried running the main query and the sub-query separately, and they both work. But using them together, I get that error:

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

What am I missing? Thanks, if you decide to help.

Solution

You probably intend to also compare the year of the BirthDate in the WHERE clause, rather than the date itself:

SELECT PP.FirstName, PP.LastName
FROM Person.Person PP
INNER JOIN HumanResources.Employee HE
    ON PP.BusinessEntityID = HE.BusinessEntityID
WHERE YEAR(BirthDate) IN (SELECT YEAR(BirthDate)         -- change is here
                          FROM HumanResources.Employee
                          WHERE YEAR(BirthDate) < 1955);