2 Replies Latest reply: Mar 28, 2012 11:17 AM by 926700 RSS

    update char values

    926700
      Hi

      I'm trying to update records to allow for clocks going forward - main obstacle being that clockin_time is a varchar

      create table CLOCKS(

      ID Number (8),
      clockin_time VARCHAR (8)
      );

      INSERT INTO CLOCKS
      VALUES (001, '07:50');

      I've been playing around with to_char and to_date but cant seem to get it quite right....

      tried

      UPDATE CLOCKS
      SET clockin_time = to_date(clockin_time +1/24, 'hh:mi')
      WHERE ID = 001;

      Im relatively new to this but any help would be appreciated!
        • 1. Re: update char values
          rp0428
          If you know you only have hours and minutes in the field you can use something like
          select to_char((to_date('07:50', 'hh:mi') + 1/24), 'hh:mi') from dual
          This
          1. converts the value to a datetime
          2. adds one hour
          3. converts the datetime back to a string

          Just use your column name instead of '07:50'

          >
          main obstacle being that clockin_time is a varchar
          >
          Seems like you already know this is a bad thing to do.
          • 2. Re: update char values
            926700
            Thank you - yes I realize it's not a smart move storing a date as a varchar - not my design!