converting varchar2 to date
Posted on 2005-05-17
I'm importing a text file that has been generated from vsam to oracle by a control file and has been scheduled ---working fine.
what i did is i created two tables and actually they are the same but the first table with varchar2 data types and the other with a specific type just to solve the problem because what i get from the text file is wrong format, so i have a column in the text file contains data for time and the format is like 01200
What i did in the first table is
update table set column= substr(column,2,4);
update table set column= to_char(substr(column,1,2)||':'||substr(column,3,2));
and it will be like 12:00
then i transferred the first table to the other table with data type of date
insert into table2
the error that appears says" ORA-01849: hour must be between 1 and 12",but if the data type of the table2 was varchar2 it will successfully imports.
and at the end can i make a formula column to subtract this column with another column with same type .
For example 12:00 - 08:00 = 4
Thanks in advance