Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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?

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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.

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading