sql - 操作数类型冲突 : date is incompatible with smallint error in sql server

Question

我编写了一个 sql 查询来获取 2006 年到 2008 年之间的员工雇佣人数。这是我来自 Adventurework2014.dimemployee 表的代码

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)

我上面的代码显示错误

Operand type clash: date is incompatible with smallint

请帮我解决一下。

Answer 1

你错过了在 where 子句中使用 year() :

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

此外，您也不需要将 cast() 函数与 year() 一起使用，因为它会返回数字类型。

你的 SELECT 语句对我来说很奇怪，当你包含 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);