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