How to subtract dates in Oracle PL SQL-CodePudding

I'm using Oracle 18c. I'm trying to determine elapsed time, but I get an error when I subtract two date variables in PL SQL. The following code works fine:

DECLARE
    l_zero_date     date;
    l_current_date  date;
    l_elapsed_time  date;
BEGIN
    Execute Immediate 'ALTER SESSION set nls_timestamp_format = "DD-MM-YYYY HH24:MI:SS"';
    l_zero_date := to_date('01-01-1900 00:00:00', 'DD-MM-YYYY HH24:MI:SS');
    dbms_output.put_line('The value of l_zero_date is: ' || l_zero_date);
    Select ls.duration Into l_current_date From LIT_STATS ls Where ls.prim_key = 1002;
    dbms_output.put_line('The value of l_curr_date is: ' || l_current_date);
--    dbms_output.put_line('The elapsed time is: ' || l_current_date - l_zero_date);

END;

This produces the results:

The value of l_zero_date is: 1900-01-01 00:00:00
The value of l_curr_date is: 1900-01-01 00:35:22

However, If I un-comment the last dbms_output line I get the error:

Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 14
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

I don't understand why I get the error on subtraction involving two fields declared as DATE. For example, the following code works fine:

declare
        a       date;
        b       date;
begin
        a := sysdate;
        dbms_lock.sleep(10);    -- sleep about 10 seconds give or take
        b := sysdate;
        dbms_output.put_line( b-a || ' of a day has elapsed' );
        dbms_output.put_line( (b-a)*24 || ' of an hour has elapsed' );
        dbms_output.put_line( (b-a)*24*60 || ' of a minute has elapsed' );
        dbms_output.put_line( (b-a)*24*60*60 || ' seconds has elapsed' );
end;

Why does the line dbms_output.put_line('The elapsed time is: ' || l_current_date - l_zero_date); produce an error?
Thanks for looking at this.

CodePudding user response：

As I mentioned in the comments, this is an order of operations issue. Take the following example:

SELECT 'TEST'||SYSDATE-SYSDATE FROM DUAL

When this runs, I get the following error: ORA-00932: inconsistent datatypes: expected CHAR got DATE

But when I wrap the dates in ( and ):

SELECT 'TEST'||(SYSDATE-SYSDATE) FROM DUAL

The result is TEST0.

It is order of operations, the code moves left to right unless there are parentheses informing it to do the date subtraction first.

Here is a DBFiddle showing the queries being run (LINK)