Select excluding certain time of the day

user_12289444 0
Viewed 29

So I'm trying to query records that don't have a specific timestamp, I don't want to see any records that have a time of 01:00:00 AM

select * from records 
  where to_char(record_time,'hh24') not between 1 and 1

But I'm still not getting the result set I'm looking for, any help is appreciated.

oracle sql
2 Answers

Try:

SELECT *
  FROM RECORDS
 WHERE NVL(TO_CHAR(record_time,'HH24:MI:SS AM'),'X') <> '01:00:00 AM';
user_48918150

You can compare the time with the time truncated back to midnight with one hour added and since NULL is never equal to anything you can test for that separately:

SELECT *
FROM   records
WHERE  record_time != TRUNC(record_time) + INTERVAL '1' HOUR
OR     record_time IS NULL;
user_15092640
Related