apply interval day to second function to character data
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: apply interval day to second function to character data

  1. #1
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322

    apply interval day to second function to character data

    I want to load data from a csv file whose one filed has data like this '02:54:27' (hh:mi:ss). This field records the activity time of an agent. Googling did not help me in finding answers to my questions.

    I have 2 questions:
    1. Is there a way I can convert such character data directly to interval day to second data type while loading using SQL loader?
    2. If the answer to 1 is No, then I will load data directly in some temporary table. Is there a function in oracle that would help in converting char data to interval day to second type within oracle so that I can load it in a different table?
    lucky

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,483

    Cool

    Did you try this in your control file?:
    Code:
    LOAD DATA 
    ... etc ...
    my_interval "TO_DSINTERVAL(:my_interval)",
    ... etc ...
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

  3. #3
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by LKBrwn_DBA View Post
    Did you try this in your control file?:
    Code:
    LOAD DATA 
    ... etc ...
    my_interval "TO_DSINTERVAL(:my_interval)",
    ... etc ...
    Thanks. Actually I could find that after searching for 2-3 hours, I almost forgot that such a function exists.
    lucky

  4. #4
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by LKBrwn_DBA View Post
    Did you try this in your control file?:
    Code:
    LOAD DATA 
    ... etc ...
    my_interval "TO_DSINTERVAL(:my_interval)",
    ... etc ...
    Is there a way in which to_dsinterval handles the following situation:

    SQL> select to_dsinterval(concat('0 ','44:34:54')) from dual;
    select to_dsinterval(concat('0 ','44:34:54')) from dual
    *
    ERROR at line 1:
    ORA-01850: hour must be between 0 and 23

    Obviously, I won't do that in a sql statement. Actually, the data from csv file comes like this: 44:20:13, 34:14:31. I have avoided converting this data to type interval day to second while loading. So, in oracle table, this data is stored as char type.

    After I load data, I need to present this data using select statement as sum of two fields. So, the data in column1(44:20:13) and column2(34:14:31) will be presented in one column as:

    select column1+column2 from ;

    The output should be +3 06:34:44 or 78:34:44(preferred).
    lucky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width