SQL & PL/SQL

Hi All,

My table having Date field with existing data .Now I want to convert that field to TIMESTAMP WITH TIME ZONE.

create table test1(date1 date);

insert into test1 values(sysdate);

alter table test1 modify date1 TIMESTAMP WITH TIME ZONE;

ORA-01439: column to be modified must be empty to change datatype

I want to update the existing date with current DBTIMEZONE like below.Here time zone is updated in Offset format +5.30.But I want to update DBTIMEZONE in region name format 'Asia/Culcutta' (Not with Offset format +5.30 ) to manage Day light saving issue.Can anyone please help.

alter table test1 add date2 TIMESTAMP WITH TIME ZONE;

update test1 set date2=CAST(date1 AS TIMESTAMP WITH TIME ZONE);

alter table test1 drop column date1;

alter table test1 rename column date2 to Date1;

Thank you.