Msg 529, Level 16, State 2, Line 1 Explicit conversion from data type int to time is not allowed

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;

I'm trying to calculate all details of calls that are pending but I'm getting an error. Reference_number is of INT data type

What do `Hold_Time` and `Duration` represent? What data is in there? Did you mean instead to perform something like `CONVERT(time(0), DATEADD(second, Hold_Time, 0))`? Show us a few sample rows of the output of those two columns **without** the `CAST` and what you actually wanted/expected to see **with** the `CAST`. — Aaron Bertrand, Sep 26 '18 at 16:27
please share some data of duration field from your actual table — Md. Zakir Hossain, Sep 26 '18 at 18:07
_customer AS c ON c.CustomerID = c.CustomerID_ Well that is wrong. — SMor, Sep 26 '18 at 18:17
We need to know the column type for Hold_Time and Duration. Then look at other messages https://stackoverflow.com/questions/41831971/convert-issue-in-sqlserver-with-msg-529-error which might point you to the answer, depending on column type of your inputs. — donPablo, Sep 26 '18 at 18:44
@smor I fixed that thanks but i'm still getting the above mentioned error — Firephantom19, Sep 27 '18 at 05:07
Note that the `time` data type isn't really appropriate for storing something called "duration". `time` represents a *time of day*, not a *time span*. Those are two separate (but of course related) concepts. `time` cannot store negative values, nor values greater than 24 hours; nor can you add two `time` values together. All of those things would make sense for time spans. None of them make sense for a time of day. — Damien_The_Unbeliever, Sep 27 '18 at 06:24

Tomek · Answer 1 · 2018-09-26T16:38:45.867

0

I guess [Duration] (and/or [Hold_Time]) is of INT type, if the value is in minutes (otherwise change accordingly), then use:

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

Since you use TIME(0) data type, value of field after casting can't be greater than 24h

edited Sep 26 '18 at 16:38

answered Sep 26 '18 at 16:32

Tomek

3,267
2
22
23

score 0 · Answer 2 · answered Sep 26 '18 at 21:53

May be this help?

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)

score 0 · Answer 3 · answered Sep 27 '18 at 05:51

I fixed it guys thank you so much

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;

Msg 529, Level 16, State 2, Line 1 Explicit conversion from data type int to time is not allowed

3 Answers3