Counting hours in format H:mm in SQL-CodePudding

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!

CodePudding user response：

what I did was a bit tricky but it worked:

WITH A AS (
    SELECT cast(substring(hours, 1, len(hours) -3) as INT) as TIME
    FROM your_table
              )

  SELECT count(*) as MORE_THAN_5_HOURS
  FROM A
  WHERE TIME >= 5

you remove the last 3 letters of the text you have as hours (8:54 will become 8) then you convert it to INT putting all of what you did in a table called A. and then you count all rows in A with TIME above 5.

CodePudding user response：

Just another option

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

Results

Cnt
5

CodePudding user response：

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 |
 ---------