Converting a date to a number for a Procedure-CodePudding

Hi i have a procedure that has in it

Select
(ROUND(A.START - A.END, 0) AS DAYS_WORKED
FROM WORKINGTABLE A

THIS RETURNS 5

super when i put it in a procedure and update another table with the DATATYPE as NUMBER the info it says this it cant do it becasue the data type is not a number it is a date. fair enough. so I changed the column to a DATETYPE but it didnt like it and gave an error saying invalid month (as the date and time is 5 im not suppriesed it didnt like it)

so I set the column back to NUMBER and tried this

TO_NUMBER(ROUND(A.START - A.END, 0))) AS DAYS_WORKED

and this

TO_NUMBER(TO_CHAR(ROUND(A.START - A.END, 0))) AS DAYS_WORKED

but it still thinks it is a date and gives this messag

ORA-00932: inconsistent datatypes: expected NUMBER got DATE.

shows fine in a query output/report it is just the procedure being fussy

any ideas how i can get this to line up?

CodePudding user response：

You did something wrong (can't tell what exactly). Have a look at the following test case.

Sample table; days_worked is - as you said - a number:

SQL> create table test (id number, days_worked number);

Table created.

SQL> insert into test (id) values (1);

1 row created.

Procedure accepts date datatype parameters. Difference of two dates is number of days between.

SQL> create or replace procedure p_test (par_id in number,
  2    par_date_start in date, par_date_end in date) is
  3  begin
  4    update test set
  5      days_worked = round(par_date_end - par_date_start, 0)
  6      where id = par_id;
  7  end;
  8  /

Procedure created.

Testing:

SQL> begin
  2   p_test(1, to_date('05.12.2022 13:43:22', 'dd.mm.yyyy hh24:mi:ss'),
  3             to_date('28.12.2022 08:23:32', 'dd.mm.yyyy hh24:mi:ss'));
  4  end;
  5  /

PL/SQL procedure successfully completed.

Result:

SQL> select * from test;

        ID DAYS_WORKED
---------- -----------
         1          23

SQL>

So, yes - it works when properly used.

CodePudding user response：

AGGGHH

It turned out it was me not realising how insert into works.

I thought it was inserting on the name of the select. it wasnt it was inserting in order of the column (something i cant change in oracle) so when it was saying it was the wrong format it was becasue the column it was supposed to be going into wasnt the right one. it ignores the names and does it in order

thanks for your help Lightfoot. it helped me look in the right direction