0
select
CREATION_DATE,
APPROVED_DATE,
REQUISITION_NUMBER,

from POR_REQUISITION_HEADERS_ALL 

where
CREATION_DATE >= '2021-4-11' 
and APPROVED_DATE Is NOT null

Is there any way to subtract APPROVED_DATE - CREATION_DATE and get the difference of days and mins and seconds into days?

The error I got when I try

inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND

  • CREATION_DATE 2021-04-13T21:27:56.089+00:00
  • APPROVED_DATE 2021-04-20T08:18:04.222+00:00

The output I want would be 6.9 days for example

There is any help thanks anyway.

Abdelrhman Ahmed
  • 3
  • 3
  • 1
    What is the data type of `creation_date` and `approved_date`? The names imply that they are dates. The error indicates that they are likely some form of timestamp but I'm not sure what code threw the error you posted. Or you could be storing the data in a `varchar2` column – Justin Cave May 23 '21 at 07:49
  • the line i got for this error are AVG(pola.dayss) xx , pola.APPROVED_DATE- add_months(pola.CREATION_DATE,trunc( months_between(pola.CREATION_DATE,pola.APPROVED_DATE ) )) dayss when I try to run it I got this error inconsistent datatypes: expected NUMBER got INTERVAL DAY TO SECOND – Abdelrhman Ahmed May 23 '21 at 08:14
  • OK. It's really helpful to edit this into the text of your question. Specifying an error without including the code that is causing the error is confusing. Back to my original question, though, what is the data type of the `creation_date` and `approved_date` columns? Again, I'm guessing from the error that they are some form of timestamp despite the name implying that they are dates. – Justin Cave May 23 '21 at 08:23
  • I don`t know what is the data type but it is date and the output are 2021-04-13T21:27:56.089+00:00 – Abdelrhman Ahmed May 23 '21 at 08:26
  • Sorry, why don't you know what the data type of the column is? Can't you look at the table definition? `date`, `timestamp`, `timestamp with time zone`, `timestamp with local time zone` and `varchar2` are all different data types and you'd need to handle them differently. – Justin Cave May 23 '21 at 08:29
  • You can find out the datatype by doing a ```DESCRIBE POR_REQUISITION_HEADERS_ALL```or by checking the documentation https://docs.oracle.com/en/cloud/saas/procurement/20d/oedmp/self-service-procurement.html#poramxapprovalgroups-22458 This will tell you that the datatype of those 2 columns is ```TIMESTAMP``` – Koen Lostrie May 23 '21 at 08:39

0 Answers0