add time (char(8)) to date column

Question

i try to merge a date and time column.

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

alter table teldat add (date_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*****');

update teldat set date_conv=(to_date(substr(date, 1, 2)+substr(date, 4, 2)+substr(date, 6, 2)+substr(time, 1, 2)+substr(time, 4, 2),'dd.mm.yy hh24:mi'));

if i execute the update statement, the following error message appears:

Error starting at line : 1 in command -
update teldat set date_conv=(to_date(substr(date, 1, 2)+substr(date, 4, 2)+substr(date, 6, 2)+substr(time, 1, 2)+substr(time, 4, 2),'dd.mm.yy hh24:mi'))
Error at Command Line : 1 Column : 45
Error report -
SQL Error: ORA-00936: missing expression
00936. 00000 -  "missing expression"
*Cause:  *Action:

come to no solution. can anyone help me?

Kind Regards

Answer 1

This update works:

update teldat set 
  date_conv = to_date(to_char(tdate, 'yyyymmdd')||uhrzeit,'yyyymmddhh24:mi');

^SQLFiddle

I assumed that you want second column (uhrzeit) for time part, but if you want next column - use it (with substr(..., 5)). Also I changed column name date to tdate because date is Oracle keyword, so it's better to avoid using it as column name.

Answer 2

Can you make the below corrections and try:

1. Change the column name from date to dates(any name) as DATE is an oracle Keyword

2. The no of values you are inserting is 7 which less than the columns you have in the table after altering(adding) 1 column (7+1 = 8). Hence add one more value.

3. Correct the update statement(see below)

alter SESSION set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS' ;

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

alter table teldat add (date_conv date);

INSERT INTO TELDAT VALUES (to_date('04.08.2011','dd.mm.yyyy'),'17:33 ', '00:00:40',10,9, 'K10', NULL,
to_date('13.08.2011','dd.mm.yyyy'));

select* from teldat;

update teldat  set date_conv= 
TO_DATE(
 substr(dates, 1, 2) || '-'  || 
 substr(dates, 4, 3) || '-'  ||
 substr(dates, 8, 4) || '-'  || 
 substr(time, 1, 2) || ':'   ||
 substr(time, 4, 2) 
 ,'DD-MON-YYYY HH24:MI')
; 

commit;

OUTPUT:

Answer 3

SQLDeveloper Menu: Tools, Preferences, Database, change NLS-Date format

update teldat set datum_conv=to_date(to_char(datum, 'dd.mm.yyyy')||uhrzeit,'dd.mm.yyyy hh24:mi');
select to_char(datum_conv, 'dd.mm.yyyy hh24:mi') from teldat ;

Works for me! Thanks for all the help guys!! :)

Kind Regards