I am using a query to left join table a with cms on common columns and doing a time comparison like this
a.col1=cms.col1 and a.col2=cms/col2 and
to_char(a.job_time::timestamp, ‘HH24:MI:SS’)::time between
(cms.scheduled_load_start-interval ‘1.5 hours’) and (cms.scheduled_load_finish+interval ‘2 hours’)

get the run no from cms table but some how only one record out of 2000 is not falling between the scheduled start and finish even though it should. Not able to understand.

The a.job_time has a value 01:00:38 and scheduled start and end have 00:00:00 and 01:00:00 if I add and subtract interval then the job_time should return run no but its returning null, not joining with the cms table because of the time part, col1 and col2 are joining w/o any issue.

Any help would be appreciated

I have tried to break down the query use >= and <= instead of between nothing is working.