Via Sql loader i loaded one sql server table data in Oracle by changing the datatype to varchar2 , Now i want to change as it is, so how to write Procedure for the same by which i can changed the data type of oracle Table? ( I was facing problem in date format so i changed it to varchar, then all data loaded proprly appx 140524 rows)
When i am trying to run below commond on oracle table where we need to change Varchar2 to date DATA type for resp column:
insert into evaluation_new (CREATEDDATE)
(Select To_date(CREATEDDATE,’yyyy-mm-dd hh24:mi:ss’)
from evaluation_detail);
Error:
ORA-01830: date format picture ends before converting entire input string
01830. 00000 – “date format picture ends before converting entire input string”
*Cause:
*Action:
(Note: Old str of column “2009-09-16 18:38:00.000”)
Above data indicates that you should be using timestamp datatype
SQL> select to_date( ‘2012-09-28 02:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS’) from dual;
select to_date( ‘2012-09-28 02:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS’) from dual
*
ERROR at line 1:
ORA-01830: date format picture ends before converting entire input string
If I change this to timestamp it would work fine
select to_timestamp( ‘2012-09-28 02:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FF’) from dual;
TO_TIMESTAMP(‘2012-09-2802:00:00.000′,’YYYY-MM-DDHH24:MI:SS.FF’)
—————————————————————————
28-SEP-12 02.00.00.000000000 AM