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