Character to number conversion error in trigger

Question

How will the trigger insert between two timings? Getting character to number conversion error in the if condition while inserting. Below is my trigger.

create or replace trigger TRI_INSERT
  after insert on stud_details  
  referencing old as old new as new
  for 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' and '23:59:59') then
             insert into 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;

What is the type of `stud_details.time_stamp`? And if it is a some variant of `char` or `varchar`, **why**? — Shannon Severance, Feb 10 '13 at 07:39

score 1 · Answer 1 · answered Feb 09 '13 at 08:54

1

you cannot to_char() a varchar2 with a date format and expect it to work.

instead you should do

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

also if you want to insert the time into the table in a specific format, use

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

as with

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

you will just be inserting the date in whatever the default NLS_DATE_FORMAT is for that session (which may vary per session).

answered Feb 09 '13 at 08:54

DazzaL

21,638
3
49
57

That all assumes that the `time_stamp` column for the `stud_details` has type `timestamp` or `date`. Given the name, one would hope so, but given the PL/SQL above, best to check first. – Shannon Severance Feb 10 '13 at 07:39
I want to insert the data, only if the :new_time_stamp has the timing in between 22:00 and 23:59. – Vasu Sambandam Feb 13 '13 at 12:17
@VasuSambandham yes, the if check does that (a char comparison, but its safe to do in this case) – DazzaL Feb 13 '13 at 12:24

score 1 · Answer 2 · answered Feb 10 '13 at 18:08

1

How about instead using:

if extract(hour from :new.time_stamp) in (22,23) then ...

answered Feb 10 '13 at 18:08

David Aldridge

51,479
8
68
96

Nice. So often I forget about the date and time specific functions. But it is often clearer and more likely to be correct to use the built in functions instead of taking pieces out through `to_char` and working with those. – Shannon Severance Feb 11 '13 at 23:37

score 0 · Answer 3 · answered Feb 23 '13 at 07:24

Problem in the if condition.. Now its working fine.. Thanks for all.

create or replace trigger TRI_INSERT

  after insert on stud_details  

  referencing old as old new as new

  for each row

declare

    strTime varchar2(20) := :new.time_stamp;   -- (eg: '02/08/2013 11:09:42 PM')
    strFromTime varchar2(20):= '22:00:00'
    strToTime varchar2(20):= '23:59:59'

begin

    if ((to_char(strTime, 'hh24:mi:ss') > strFromTime) and (to_char(strTime,           
         'hh24:mi:ss') < strToTime)) then
         insert into 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;

Character to number conversion error in trigger

3 Answers3