Msg 529，级别 16，状态 2，第 1 行 不允许从数据类型 int 显式转换为 time

Question

SELECT 
Reference_Number,
c.First_Name + ' ' + c.Last_Name AS 'Customer_Name',
Staff_FirstName + ' ' + Staff_LastName AS 'Staff_Name',   
Date_Time, Summary, OtherDetails,
CAST(Hold_Time AS TIME(0)) AS 'HoldTime',
CAST(Duration AS TIME(0)) AS 'Duration',
Call_Status         
FROM
Support_Call AS tc 
INNER JOIN 
customer AS c ON c.CustomerID = c.CustomerID
INNER JOIN 
staff AS s ON tc.StaffID = s.StaffID
WHERE 
Call_Status LIKE 'Pending%'
ORDER BY 
Date_Time DESC;

我正在尝试计算待处理呼叫的所有详细信息，但出现错误。Reference_number数据类型INT

Answer 1

我猜 [持续时间](和/或 [Hold_Time](是 INT 类型，如果值以分钟为单位(否则相应更改(，则使用：

CAST(DATEADD(mi, Duration, CAST(0 AS DATETIME)) as time(0)) AS 'Duration'

由于使用 TIME(0( 数据类型，因此转换后的字段值不能大于 24h

Answer 2

这可能是帮助吗？

Declare @Datum varchar(10)='3:00:00 PM'
--Convert it into Hour as integer 
Select  (cast (  SUBSTRING(@Datum, 1, CHARINDEX(':', @Datum, 1)-1)     as numeric (10,2))  +Cast ( (SUBSTRING(@Datum,(CHARINDEX(':', @Datum, 1)+1),2)) as numeric (10,2))/60)

Answer 3

我修好了，非常感谢

SELECT Reference_Number,
First_Name+' '+Last_Name AS 'Customer_Name',
Staff_FirstName+' '+Staff_LastName AS 'Staff_Name',    
Date_Time,Summary, OtherDetails,
Call_Status      
FROM Support_Call AS tc INNER JOIN customer AS c
ON c.CustomerID = c.CustomerID
INNER JOIN staff AS s
ON tc.StaffID =s.StaffID
WHERE Call_Status LIKE 'Pending%'
ORDER BY Date_Time DESC;