SQL & PL/SQL

hi guys

i have a table and date value in a column is stored in varchar2(35)

it is stored to take time upto milliseconds like below


select transaction_date from table

09/09/2009 12:43:04:651

now i want to convert the datatype from vatchar2 to timestamp in my table

so that the data remain as its and start excepting new data according to timestamp datatype

i dont know why earlier developers have taken varchar instead of timestamp

our production server is in US and data entered through India and business also

So they used varchar datatype to store milliseconds
and use a procedure to have time in IST

now could i change the datatype to timestamp without affecting data

as i think its really required and its not good to store date in varchar

the procedure i know to change datatype is as below


1.Create Table smsrec_dup As Select Rowid Row_Id, transaction_date From sms_rec;


2.Update sms_rec Set transaction_date = Null;


3.Commit;


4.Alter Table sms_rec Modify transaction_date timestamp(3);


5.Update sms_rec B Set transaction_date = ( Select transaction_date From smsrec_dup A Where B.Rowid = A.Row_Id);


6.Commit;


above changing the datatype TO TIMESTAMP

NOW I WANT HOW COULD I INSERT TIME IN IST

PRESENTLY developers are using a procedure for IST

AS

CREATE OR REPLACE PROCEDURE current_tstamp (actual out varchar2)
AS
var_sign varchar2(2);
var_zone varchar2(35);
time TIMESTAMP;

BEGIN

select substr(systimestamp, 30,1),to_char(systimestamp, 'tzr') into var_sign, var_zone from dual;

IF var_sign='+' THEN

IF var_zone = '+05:30' THEN

SELECT to_char(systimestamp, 'dd/mm/yyyy hh24:mi:ss:ff3') into actual from dual;
dbms_output.put_line (actual);

ELSE

select to_char(systimestamp+(1/1440 * (330-(to_number(substr(systimestamp, 31,2))*60 +
to_number(substr(systimestamp, 34,2))))), 'dd/mm/yyyy hh24:mi:ss')
||':'||
to_char(systimestamp, 'ff3') ,
systimestamp
into actual, time
from dual;

dbms_output.put_line (actual);

END IF;

ELSE

select
to_char(systimestamp +
(1/1440 *
(to_number(substr(systimestamp, 31,2))*60 +
to_number(substr(systimestamp, 34,2)) + 330)),'dd/mm/yyyy hh24:mi:ss')
||':'||
to_char(systimestamp, 'ff3') ,
systimestamp
into actual, time
from dual;

dbms_output.put_line (actual);

END IF;

END;
/


and calling this in trigger

as
CREATE OR REPLACE TRIGGER "TEST".trg_tctn_dtinsrt_sms
BEFORE INSERT ON sms_received
FOR EACH ROW
DECLARE
DT VARCHAR2(55) ;
BEGIN
current_tstamp(DT) ;
:new.transaction_date := DT;
END;