Search code examples
sqlsql-servercalculated-columnshour

Get the total sum hours in a column SQL SERVER

Sql Fiddle Example

I have this result table

 Id     Hours
 -----   -----
 1       09:00
 2       09:30
 3       10:00
 4       10:30
 5       11:00
 6       11:30
 7       12:00
 8       12:30
 9       13:00
10       13:30
11       14:00
12       14:30
13       15:00
14       15:30
15       16:00
16       16:30
17       17:00
18       17:30
19       18:00

I need to get the total sum hours, for example from 09:00 to 18:00 there is a total of :

9

hours, I need to get this sum of hours

Solution

  • Your table schema hour is varchar, you need to cast as time, then do the calculation

    SELECT  datediff(hour,min(cast(hour as time)),max(cast(hour as time)))
FROM Timetable

    sqlfiddle

    NOTE

    I would suggest your hour column as datetime or time instead of varchar. because hour column intention is time.

    EDIT

    If your time is 9:00 to 17:30, you can try to use datediff minute to get the total diff minutes then divide 60 to get hours.

    SELECT datediff(minute,min(cast(hour as time)),max(cast(hour as time))) / CAST(60 as float)
FROM Timetable

    https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=6e005cdfad4eca3ff7c4c92ef14cc9c7