converting varchar2 to date

Posted on 2005-05-17
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
    LVL 9

    Expert Comment

    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.

    LVL 9

    Expert Comment

    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

    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 is this the problem.
    LVL 27

    Expert Comment

    what about

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

    just as a variante

    meikl ;-)
    LVL 31

    Accepted Solution

    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 34

    Expert Comment

    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

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
    Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
    This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now