select ACTUAL_SETTLEMENT_DATE,
to_char(CREATED_TMSTMP+ interval '480' MINUTE,'yyyy-mm-dd hh24:mi:ss'),
to_char(CREATED_TMSTMP+ interval '480' MINUTE,'HH24') hours,
to_char(CREATED_TMSTMP+ interval '480' MINUTE,'MI') mins,
to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'HH24')) num_hr,
to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'MI')) num_min
from transaction
where transaction_status_code='ACCEPTED'
AND TO_CHAR(ACTUAL_SETTLEMENT_DATE,'YYYYMMDD') ='20121212'
AND INSTRUCTING_BANK_CODE='CITISGS0'
AND PAYMENT_TYPE_CODE='01'
and (to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'HH24'))>=0
and to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'HH24'))<=23)
and (to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'MI'))>=0
and to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'MI'))<=59)
to_char(CREATED_TMSTMP+ interval '480' MINUTE,'yyyy-mm-dd hh24:mi:ss'),
to_char(CREATED_TMSTMP+ interval '480' MINUTE,'HH24') hours,
to_char(CREATED_TMSTMP+ interval '480' MINUTE,'MI') mins,
to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'HH24')) num_hr,
to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'MI')) num_min
from transaction
where transaction_status_code='ACCEPTED'
AND TO_CHAR(ACTUAL_SETTLEMENT_DATE,'YYYYMMDD') ='20121212'
AND INSTRUCTING_BANK_CODE='CITISGS0'
AND PAYMENT_TYPE_CODE='01'
and (to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'HH24'))>=0
and to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'HH24'))<=23)
and (to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'MI'))>=0
and to_number(to_char(CREATED_TMSTMP+ interval '480' MINUTE,'MI'))<=59)