extract day from TO_DSINTERVAL

You need to have a datetime value in the extract() function.

Try extract(day from some_date+elapsed_time) instead of extract(day from elapsed_time).

You will also need to change your variable data type.

create or replace function Elapsed_DaysFormula  
return NUMBER is

-- change datatype
--days_elapsed project_consultant.elapsed_time%type;
days_elapsed varchar2(20);


     CURSOR days_cursor IS
-- add interval expression that resolves to a date.
       SELECT extract(day from sysdate-elapsed_time) elapsed_time
        FROM project_consultant;

     days_row days_cursor%RowType;

BEGIN


     FOR days_row IN days_cursor
     LOOP
       days_elapsed := days_row.elapsed_time;
       return days_elapsed;
     END LOOP;

END;

Sorry, your question, and function description do not match.

Could you tell me what is the datatype of project_consultant.elapsed_time ?

Assuming your datatype is INTERVAL DAY TO SECOND. And you need to find the day part in the interval.

You cannot use the EXTRACT function, because it works on a DATE field, and not on the interval field.

Try my example first :
SQL> desc tab_dsint
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ELAPSED_TIME                                       INTERVAL DAY(3) TO SECOND(6)

SQL> select * from tab_dsint;

ELAPSED_TIME
---------------------------------------------------------------------------
+100 10:00:00.000000
+001 00:00:00.000000
-050 10:00:00.000000
+000 12:40:00.000000

create or replace function Elapsed_DaysFormula
return NUMBER is
days_elapsed varchar2(10);
CURSOR days_cursor IS
select substr(elapsed_time,1,instr(elapsed_time,' ')) elapsed_days from tab_dsint;
begin
for c1_row in days_cursor
loop
select c1_row.elapsed_days into days_elapsed from dual;
return days_elapsed;
end loop;
end;
/

SQL> select Elapsed_DaysFormula from dual;

ELAPSED_DAYSFORMULA
-------------------
                100

---------------
Taking the same approach, lets look at a solution for you :
create or replace function Elapsed_DaysFormula
return NUMBER is
days_elapsed varchar2(10);
CURSOR days_cursor IS
select substr(elapsed_time,1,instr(elapsed_time,' ')) elapsed_days from project_consultant;
begin
for days_row in days_cursor
loop
select days_row.elapsed_days into days_elapsed from dual;
return days_elapsed;
end loop;
end;
/

Hope this helps. Please let me know if you have any more questions.

Note : You are selecting the INTERVAL field from a table, but you are returning only the day part of first interval that you get. Though this table might have more than one rows. Is this what you want ?

 

Here's the solution I came up with and it works, extracting the day part from an dsinterval data type:

       days_elapsed NUMBER(10);      

     CURSOR days_cursor IS
       SELECT extract(day from elapsed_time)elapsed_time
        FROM project_consultant
        WHERE elapsed_time = :elapsed_time;

     days_row days_cursor%RowType;

BEGIN


     FOR days_row IN days_cursor
     LOOP
       days_elapsed := days_row.elapsed_time;
     
     END LOOP;
  return days_elapsed;
END;

So your original error was corrected by specifying an alias: "elapsed_time"

It looks like extract(day from elapsed_time) returns the number of days in the interval.

Unless there is a unique constraint on elapsed_time, then your loop will return the last record in the cursor.  In other words, if multiple records in project_consultant table have the same elapsed_time, then your cursor will loop through all of the qualifying records and return the last record to the caller.  It seems like you would want to be more certain about which record you return.

this can be accomplished by just doing:
EXTRACT( DAY FROM PROJECT_CONSULTANT.ELAPSED_TIME ) "Elapsed Time"  

please close or delete, thank you.

Please close this question Q_20974993.html

Please close this question.

Hi Barbara, please raise a 0 point question in the Community Support TA at https://www.experts-exchange.com/Community_Support/ 

They would be able to close this question and refund your points.

I'm not understanding what you're telling me to do.

Refer to help page at https://www.experts-exchange.com/help.jsp#hi9

thanks