Select excluding certain time of the day-CodePudding

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.

CodePudding user response：

Try:

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

CodePudding user response：

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;