创建vwPayroll视图报错time与tinyint不兼容，如何计算OTRate倍半薪资

报错原因 #

Msg 206, Level 16, State 2, Line 1
Operand type clash: time is incompatible with tinyint

该报错的根源是OTRate列的赋值逻辑错误：你当前用CONVERT(Time, GETDATE())生成了时间类型的值，但OTRate作为加班薪资倍率，需要存储数值类型的倍率数据，二者类型不匹配就触发了类型冲突报错。

修正方案 #

要实现1.5倍加班薪资倍率计算，直接从Calculated表取标准时薪字段（你的代码中已查询PayRate作为标准时薪）乘以1.5即可，将原代码中CONVERT(Time, GETDATE()) AS OTRate替换为Calculated.PayRate * 1.5 AS OTRate即可，如果Calculated表中存在专门的加班核算基数字段，替换PayRate为对应字段即可。

创建vwPayroll视图的完整可运行代码如下：

CREATE VIEW vwPayroll AS
SELECT 
    PerFrom, PerThru, PayID, PayPeriod.PPID, 
    FirstName + LastName AS EmpName,
    PayRate, Calculated.PayRate * 1.5 AS OTRate, 
    WorkHours, HolHours, SickHours,
    VacHours, PersHours, OTHours, 
    BaseCost, SpouseCost, DepCost, DentalCost,
    Spouse, Dependants, WHAllowance, SpouseIns, DepIns, TaxRate
FROM                     
    dbo.Calculated 
CROSS JOIN
    dbo.Benefits 
INNER JOIN
    dbo.EmpData ON dbo.Benefits.BenPlanID = dbo.EmpData.BenPlanID 
INNER JOIN
    dbo.PayPeriod 
INNER JOIN
    dbo.Hours ON dbo.PayPeriod.PPID = dbo.Hours.PPID 
INNER JOIN
    dbo.[Work] ON dbo.Hours.WorkID = dbo.[Work].WorkID 
      ON dbo.EmpData.EmpID = dbo.[Work].EmpID
WHERE 
    OTHours <= 32;

时间相关的逻辑可按照你的实际需求自行调整。

内容的提问来源于stack exchange，提问作者rylew0925