-1

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

Here are some example values :

arrival_time
6.23
6.58
5.51

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?

Louis Chopard
  • 334
  • 1
  • 11
  • It would have assumed 6.23 hours was 6 hours 13.8 minutes. What time is 6.85? Why not store times using a `time` data type? – Stu Oct 01 '21 at 14:46
  • 1
    If that's SQL Server, it doesn't make sense. `time` represents a *time of day*. It makes no sense (and so isn't supported) to add two times of day together. You need something representing a time *span*, and the most sensible way of storing that would usually be an integer counting the smallest units you want to work with (e.g. minutes), that then naturally support addition. – Damien_The_Unbeliever Oct 01 '21 at 14:47
  • Sorry. I added the RDBMS. @Stu Because I got those data from a software and I can't change the database structure. – Louis Chopard Oct 01 '21 at 14:54
  • People do store time as a decimal sometimes, but then it's a *decimal*, that is, `6.25` is 15 minutes past 6 and `6.50` is 30 minutes past 6. This format is extra weird and prompts for a varchar of `'6.25'` rather than a decimal. Well then, [separate the fraction](https://stackoverflow.com/q/12129227/11683) and declare it minutes, and add all that to the zero date. – GSerg Oct 01 '21 at 15:30
  • @LouisChopard - What format do you want if the sum of the time is greater than 24 hours? – Jeff Moden Oct 02 '21 at 04:35
  • @JeffModen Hours ! Like 234 hours not day format – Louis Chopard Oct 04 '21 at 06:16

1 Answers1

0

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: enter image description here

Clifford Piehl
  • 483
  • 1
  • 4
  • 11