'Counting hours in format H:mm in SQL

I have a column on hours looking like that:

|time|
--------------
8:54
5:32
4:34
7:12
12:55
10:32
3:54
2:35

I want to count how many rows I have with more than 5 hours? something with the format of the hours makes it problematic for me.. the format is varchar (text).

how can I do it? any thought?

Thanks!

sqlsql-server


Solution 1:[1]

Just another option

Select Cnt = count(*)
 From  YourTable
 Where try_convert(decimal(10,2),replace([time],':','.')) > 5

Results

Cnt
5

Solution 2:[2]

Please try the following solution.

  • 1st step: convert data into TIME(0) data type.
  • 2nd step: get hour part of it via DATEPART(hour, ...) built-in function.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE  (ID INT IDENTITY PRIMARY KEY, hour_min VARCHAR(10));
INSERT INTO @tbl (hour_min) VALUES
('8:54'),
('5:32'),
('4:34'),
('7:12'),
('12:55'),
('10:32'),
('3:54'),
('2:35');
-- DDL and sample data population, end

-- just to see
SELECT * 
    , _hour = DATEPART(hour, TRY_CAST(hour_min + ':00' AS TIME(0)))
FROM @tbl;

-- real deal
SELECT [counter] = COUNT(*)
FROM @tbl
WHERE DATEPART(hour, TRY_CAST(hour_min + ':00' AS TIME(0))) >= 5;

Result

+---------+
| counter |
+---------+
|       5 |
+---------+

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 John Cappelletti
Solution 2

Related Questions