Search code examples
sqloracle-databaseprocedure

Converting a date to a number for a Procedure

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?

Solution

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

    I thought it was inserting on the name of the select, but it wasn't. It was inserting in order of the column (something I can't change in oracle), so when it was saying it was the wrong format, it was because the column it was supposed to be going into wasn't the right one. It ignores the names and does it in order.

    Thanks to Lightfoot for pointing me in the right direction.