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!

>Solution :

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.

Leave a Reply