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?
... etc ...
... 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).