converting varchar2 to date

Posted on 2005-05-17
Medium Priority
Last Modified: 2008-01-09
Hi experts,

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
from table1;

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
Question by:bduhaish

Expert Comment

ID: 14016883
insert into table2
from table1;

is your table2 has only one column that has date type..

update table set column=  to_char(substr(column,1,2)||':'||substr(column,3,2));

be sure this statement create 12:00 format, note that you should trim the value too, cause there might be space before 12:00 or after.

i think that should work. you can try this to verify:
SQL>select to_date('23:56','HH24:mi') from dual;
should work.

Next thing, please don't make things complex that it is.
like by subtracting column field......................

you say that you are import from flat file using control file.

then there is no reason for importing it to temporary table.
You can directly transfer it to your production table.
But you say the issue is due to datefield right??
For this you can directly convert while importing from flat file.
This can be done this by just editing your control file.


Expert Comment

ID: 14016912
A sample control file look like this:





trailing nullcols




     PROD_DATE TO_DATE(:PROD_DATE,'HH24:mi:ss'),


     terminated by whitespace)


Note line
     PROD_DATE TO_DATE(:PROD_DATE,'HH24:mi:ss'),

'HH24:mi:ss' ---> should be the format in your flat file.

Hope this may help


Author Comment

ID: 14016972
Metanil ,

first of all i didnot make things complex that it is.the major thing i want to reach at is after i get a real data i want to now the deferent time between these columns.

second the table has a lot of columns and  actullay what i want to solve is two columns of data type time from the flat file

third i did wat you said a bout shifting spaces
update table2 set column= rtrim(column);

but same error.

finally what i figured is the value of the column  like   1 to 12 am   and 13 to 24 pm , and when i change the format 'HH24:mi'  to 'HH12:mi'   it shows  these error ORA-01849: hour must be between 1 and 12  ----- OR ----- ORA-01849: hour must be between 0 and 23.so is this the problem.
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 27

Expert Comment

ID: 14017619
what about

select to_date(substr('01200',-4),'HH24MI') from dual

just as a variante

meikl ;-)
LVL 32

Accepted Solution

awking00 earned 2000 total points
ID: 14020038
You can do this all in one step.
insert into table2
select to_date(substr(col1,2,2)||':'||substr(col1,4,2),'HH24:MI')
from table1;
However, your error seems to be coming from the data which must contain numeric characters outside of the allowable limits (you should have actually shown an ORA-01850 error - hour not between 1 and 24). To test the data try -
select count(*)
from table1
where substr(col1,2,2) > 23
or substr(col1,2,2) < 0;
LVL 35

Expert Comment

by:Mark Geerlings
ID: 14022494
I've often had trouble with loading ASCII data that is supposed to contain valid dates directly into an Oracle date column.  Often these kind of data files contain data that Oracle cannot convert automatically in every case to a valid date.  I usually end up with a two-step process, more like what you described: first load the "date" info into a varchar2 column, then use SQL command(s), PL\SQL procedure(s) and/or manual editing to convert/correct the bad dates.

The data you described looks like time information only.  Oracle date columns can include the time, but they always contain a date.  If you use to_date with a format mask like "HH24:MI" be aware that Oracle will include a default date as well as the time that you see, and that default date may cause problems if this process ever runs over midnight especially if that midnight is at month end.

Author Comment

ID: 14024125
awking00 ,thanks
Problem been solved
like you said I found 16 record contains 24 for the two digits and 25 record are 0000 so I updated this columns to a temporary value time until i discussed with the user  to update the records in the vsam .

markgeer ,thanks
You are right, i have some text file that don’t be imported unless i cut the first 10 rows and paste them at the end of file

kretzschmar ,thanks

Metanil  ,thanks


Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month16 days, 19 hours left to enroll

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question