oracle - Character to number conversion error in trigger -

oracle - Character to number conversion error in trigger -

how trigger insert between 2 timings? getting character number conversion error in if status while inserting. below trigger.

create or replace trigger tri_insert after insert on stud_details referencing old old new new each row declare strtime varchar2(20) := :new.time_stamp; -- (eg: '02/08/2013 11:09:42 pm') begin if (to_char(strtime, 'hh24:mi:ss') between '22:00:00' , '23:59:59') insert stud_clas_details (id, v-id, w-id, al_id, time, time_stamp) values (seq_ve_id.nextval, :new.vehicle_id, :new.way_id, 'xxxx', strtime, sysdate); end if; end tri_insert;

you cannot to_char() varchar2 date format , expect work.

instead should do

if (to_char(:new.time_stamp, 'hh24:mi:ss') between '22:00:00' , '23:59:59')

also if want insert time table in specific format, utilize

to_char(:new.time_stamp, 'hh24:mi:ss')

as

strtime varchar2(20) := :new.time_stamp;

you inserting date in whatever default nls_date_format session (which may vary per session).

oracle if-statement plsql triggers to-char