This discussion is archived
2 Replies Latest reply: Mar 28, 2012 9:17 AM by 926700 RSS

update char values

926700 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thank you - yes I realize it's not a smart move storing a date as a varchar - not my design!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points