-
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
-
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
-
Originally Posted by LKBrwn_DBA
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
-
Originally Posted by LKBrwn_DBA
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|