I have a table with times. Inside this I have a column arrival_time which is a decimal

Here are some example values :

So 6.23 is for 6 hours and 23 minutes.

I want to sum the values as time and not as decimal

So I tried this

datediff(hour,min(cast(arrival_time as time)),max(cast(arrival_time as time))),

And I got this error

Explicit conversion from data type decimal to time is not allowed.

I didn't find how to convert the type and then sums the values.

What I am doing wrong?

CodePudding user response：

I'm going to put this here as an idea. You can use the CAST(col AS INT) function to pry out the values on either side of the decimal.

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
    DROP TABLE #TEMP

CREATE TABLE #TEMP (
    arrival_time                DECIMAL(4,2)
)
INSERT INTO #TEMP (arrival_time)
VALUES (6.23)
    ,(6.58)
    ,(5.51)

SELECT 
    arrival_time
    ,CAST(arrival_time AS INT) [Hours]
    ,CAST(arrival_time AS INT)*60 [Hours in Minutes]
    ,(arrival_time - CAST(arrival_time AS INT))*100 [Decimal Minutes]
    ,((CAST(arrival_time AS INT)*60) (arrival_time - CAST(arrival_time AS INT))*100) [Put them together for total minutes]
    --You can use the last column for a dateadd equation if you have a start date.
FROM #TEMP

Output looks like: