CODEWITHSUNDEEP
sqloracle-databasedatetype-conversion
MaCXyLo
MaCXyLo

Reputation: 5

Oracle - convert char to date

I try to convert a old time char(8) column to a new time_conv date column.

The String column have the following format:

Hour (0-99):Minute (0-59):Second (0:59)

For Example: 01:32:56

The following code does not work for me:

update teldat set time_conv=to_date(time,'hh24:mi:ss');

Edit:

CREATE TABLE teldat(
    datum       DATE,
    uhrzeit     CHAR(8),
    time        CHAR(8),
    teilnehmer  NUMBER(3),
    verbart     NUMBER(1),
    aufbauart   CHAR(3),
    ziel        VARCHAR(15));

alter table teldat add (time_conv date);

INSERT INTO TELDAT VALUES (to_date('04.08.2011'),'17:33 ', '00:00:40',10,9, 'K10', NULL); 
INSERT INTO TELDAT VALUES (to_date('04.08.2011'),'18:50 ', '00:01:41',13,9, 'K10', NULL); 
INSERT INTO TELDAT VALUES (to_date('04.08.2011'),'19:10 ', '00:02:17',21,1, 'G1 ', '01019012896****'); 
INSERT INTO TELDAT VALUES (to_date('04.08.2011'),'19:31 ', '00:11:01',10,9, 'K10', NULL); 
INSERT INTO TELDAT VALUES (to_date('04.08.2011'),'19:52 ', '00:09:47',20,1, 'G11', '077202****'); 
INSERT INTO TELDAT VALUES (to_date('04.08.2011'),'19:49 ', '10:07:02',21,1, 'G1 ', '01019012896****'); 
INSERT INTO TELDAT VALUES (to_date('04.08.2011'),'19:58 ', '00:02:41',21,1, 'G1 ', '01019012896****'); 
INSERT INTO TELDAT VALUES (to_date('04.08.2011'),'20:01 ', '00:02:31',21,1, 'G1 ', '01019012896****'); 
INSERT INTO TELDAT VALUES (to_date('05.08.2011'),'09:03 ', '00:03:02',11,9, 'K10', NULL); 
INSERT INTO TELDAT VALUES (to_date('05.08.2011'),'09:13 ', '00:03:31',10,1, 'G10', '071174****'); 
INSERT INTO TELDAT VALUES (to_date('05.08.2011'),'09:39 ', '00:06:45',13,1, 'G10', '0711707*****');

Upvotes: 0

Views: 347

Answers (2)

MaCXyLo
MaCXyLo

Reputation: 5

This code works for me :) 

  update teldat set time_conv=(to_number(substr(time, 1, 2)) * 60 * 60 +
        to_number(substr(time, 4, 2)) * 60 +
        to_number(substr(time, 7, 2))
       );

Thank you all guys!

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

If you want to convert the value to seconds, then just use arithmetic:

select (to_number(substr(time, 1, 2)) * 60 * 60 +
        to_number(substr(time, 4, 2)) * 60 +
        to_number(substr(time, 7, 2))
       ) as seconds

Upvotes: 1

Related Questions