CODEWITHSUNDEEP
sql
user1008537
user1008537

Reputation:

Get rows in between hours with SQL

I have thousands of records (rows) that contain a time column.

I can easily get rows with time's in between 4 AM and 5 AM by doing this:

SELECT * FROM MyTable
WHERE CAST(Time AS TIME) BETWEEN '04:00' and '05:00'

To get the rows between 6PM and 4AM:

SELECT * FROM MyTable
WHERE CAST(Time AS TIME) BETWEEN '18:00' and '04:00'

Gives me zero rows..

How can I do this?

Upvotes: 0

Views: 111

Answers (2)

dnoeth
dnoeth

Reputation: 60513

Casting a time to a time is strange, but I assume it's actually a Timestamp :-)

You might also use EXTRACT:

SELECT *
FROM MyTable
WHERE EXTRACT(HOUR FROM Time) >= 18
  AND EXTRACT(HOUR FROM Time) < 4

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Try:

SELECT *
FROM MyTable
WHERE CAST(Time AS TIME) NOT BETWEEN '04:00' AND '18:00'

The values to between (and not between) need to be in order, with the smaller value first. Otherwise, nothing will match.

The expression:

x between a and b

is exactly equivalent to

((x >= a) and (x <= b))

Upvotes: 0

Related Questions