How to form oracle function - TIMESTAMP input -Date Math - Number Return

Question

I am trying to create an oracle function that takes a TIMESTAMP input, calculates number of days between that timestamp and today, and returns that number. I am not constructing it properly, and not sure how to fix:

create or replace FUNCTION "DAYSSINCEOPENDATE" (OPENDATE IN TIMESTAMP) 
   RETURN NUMBER
   AS
   retval  NUMBER;

        BEGIN
           SELECT (SYSDATE - CAST(OPENDATE AS DATE) FROM DUAL)) into retval;
           return retval;
        END;

Any help is appreciated. Thanks!

Answer 1

As @Aleksej said in a comment, the order of your clauses is incorrect; you need

SELECT SYSDATE - CAST(OPENDATE AS DATE) into retval FROM DUAL;

So:

create or replace FUNCTION "DAYSSINCEOPENDATE" (OPENDATE IN TIMESTAMP) 
   RETURN NUMBER
   AS
   retval  NUMBER;

        BEGIN
           SELECT SYSDATE - CAST(OPENDATE AS DATE) into retval FROM DUAL;
           return retval;
        END;
/

select DAYSSINCEOPENDATE(timestamp '2018-01-01 12:13:14.5') from dual;

DAYSSINCEOPENDATE(TIMESTAMP'2018-01-0112:13:14.5')
--------------------------------------------------
                                        204.773368

You don't actually need the retval variable or the query against dual though; you can simplify that to

create or replace FUNCTION "DAYSSINCEOPENDATE" (OPENDATE IN TIMESTAMP) 
RETURN NUMBER
AS
BEGIN
   return SYSDATE - CAST(OPENDATE AS DATE);
END;
/

select DAYSSINCEOPENDATE(timestamp '2018-01-01 12:13:14.5') from dual;

DAYSSINCEOPENDATE(TIMESTAMP'2018-01-0112:13:14.5')
--------------------------------------------------
                                        204.773368