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

Below statement will take HireDate as string, which can't be converted to int.

cast('HireDate' as int)

Ideally you don't need to convert it into int, if you use YEAR on date, it will give you int only.

Change your query like following.

SELECT YEAR(HireDate), DepartmentName,
            count(ParentEmployeeKey) AS 'total emplyee join' 
    FROM DimEmployee 
    where YEAR(HireDate) >= 2006 and YEAR(HireDate) <= 2008 
    group by DepartmentName, HireDate,FirstName,ParentEmployeeKey
    ORDER BY  YEAR(HireDate)