Operand type clash: date is incompatible with smallint error in sql server

Question

i have write a sql query to fetch employee hire count between 2006 to 2008 . here is my code from adventurework2014.dimemployee table

SELECT YEAR(cast('HireDate' as int)), DepartmentName,
        count(ParentEmployeeKey) AS 'total emplyee join' 
FROM DimEmployee 
where HireDate between 2006 and 2008 
group by DepartmentName, HireDate,FirstName,ParentEmployeeKey
ORDER BY  YEAR(HireDate)

my above code showing error

Operand type clash: date is incompatible with smallint

please help me with some solution .

Answer 1

You missed to use year() in where clause :

where year(HireDate) >= 2006 and year(HireDate) <= 2008 

Further more you also don't need to use cast() function with year() as it will return numeric type.

Your SELECT statement is strange for me it should have aggregated column when you include GROUP BY :

SELECT YEAR(HireDate), DepartmentName,
       count(ParentEmployeeKey) AS 'total emplyee join' 
FROM DimEmployee 
WHERE year(HireDate) >= 2006 and year(HireDate) <= 2008 
GROUP BY DepartmentName, YEAR(HireDate), FirstName, ParentEmployeeKey
ORDER BY YEAR(HireDate);