Subtracting Dates ORA 01722

Question

I am attempting to subtract two dates and get a integer value in return. I have seem to hit a roadblock since one of the two dates is null which subsequently returns an error. Is there any workaround to this, I covet to get the aging_date instead if the review_date is null.

select to_date(sysdate)aging_date,to_char(review_date,'MM/DD/YYYY')review_date
from mytable    

aging_date           review_date
2/26/2020             01/05/2020
2/26/2020             05/15/2018
2/26/2020                
2/26/2020             03/14/2019
2/26/2020             12/17/2019

select aging_date,review_date,(aging_date - review_date)actual_date from
(
select 
to_date(sysdate)aging_date,to_char(review_date,'MM/DD/YYYY')review_date, 
(aging_date - review_date)actual_date from mytable
)new

ORA 01722: Invalid Number

Can this resolve https://stackoverflow.com/questions/28406397/datediff-function-in-oracle ? — TDuong, Feb 26 '20 at 17:59
@TDuong, the issue is that there is `NULL` in the review_date column. — Jake Wagner, Feb 26 '20 at 18:01
SYSDATE is a **date** and therefore doesn't require a `to_date()` conversion. Likewise, according to your comment, REVIEW_DATE is apparently a string and so doesn't require a `to_char()` conversion. Wouldn't it have been simpler and less confusing to everybody if you had just posted the DDL for `mytable` (or a redacted test case version of it)? — APC, Feb 26 '20 at 18:19
@JakeWagner - no: ["The data type of the returned value is DATE"](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/SYSDATE.html). But even if you had a timestamp - say from `systimestamp` - you wouldn't use `to_date()` to convert it, you would `cast()` it. — Alex Poole, Feb 26 '20 at 18:37

Marmite Bomber · Accepted Answer · 2020-02-26T18:23:26.540

You must convert the varchar2 column in DATE column.

select aging_date,review_date,
(aging_date - to_date(review_date,'mm/dd/yyyy')) actual_date 
from tab;

AGING_DATE          REVIEW_DAT ACTUAL_DATE
------------------- ---------- -----------
26.02.2020 00:00:00 01/05/2020          52
26.02.2020 00:00:00 05/15/2018         652
26.02.2020 00:00:00                       
26.02.2020 00:00:00 03/14/2019         349
26.02.2020 00:00:00 12/17/2019          71

Subtracting DATE and VARCHAR2 leads to an ORA-01722: invalid number

select aging_date,review_date,
(aging_date - review_date) actual_date 
from tab;

-- fails with
-- ORA-01722: invalid number

-- DDL
CREATE TABLE  TAB
   ("AGING_DATE" DATE, 
    "REVIEW_DATE" VARCHAR2(10)
   )

score 1 · Answer 2 · edited Feb 27 '20 at 19:18

1

The goal is to select the aging date, review date and the difference = (aging_date - review_date). The query selects the aging_date and the review date, and for the date difference, The case statement calculates the difference only when aging_date and review_date are not null. You can also use the if - end if block instead of case here:

select 
aging_date,
review_date,
(case when aging_date is not null and review_date is not null then (aging_date - review_date) end )actual_date  
from mytable;

edited Feb 27 '20 at 19:18

answered Feb 26 '20 at 18:07

Deepika

296
1
2

The goal is to select the aging date, review date and the difference = (aging_date - review_date). The query selects the aging_date and the review date and for the date difference, The case statement calculates the difference only when aging_date and review_date are not null. You can also use the if - end if block instead of case here. – Deepika Feb 27 '20 at 15:32

score 0 · Answer 3 · answered Feb 26 '20 at 18:00

0

Try this. should return a zero value if there is no review_date (just subtracting aging_date from itself)

select aging_date,
       review_date,
       (aging_date - nvl(review_date,aging_date)) actual_date from mytable

documentation on the NVL function.

answered Feb 26 '20 at 18:00

OldProgrammer

12,050
4
24
45

Thanks @OldProgrammer, unfortunately it still returns the same pesky `ORA 01722` error. – Jake Wagner Feb 26 '20 at 18:03
what are the data types of aging_date and review_date? Are they DATE or something else? – OldProgrammer Feb 26 '20 at 18:06
review_date has `to_char` prefix and the aging_date is just the `to_date(sysdate)` meaning today's date. – Jake Wagner Feb 26 '20 at 18:10
2

OK so what is the data type of review_date in the table??? you are omitting some details I fear. Please edit the question and show the table definition. – OldProgrammer Feb 26 '20 at 18:13
The question has been fully edited to portray old `data_type` changes. – Jake Wagner Feb 26 '20 at 18:19
1

@JakeWagner - "The question has been fully edited to portray old data_type changes" No it has not. I see no DDL showing the actual data types of the referenced columns. Only your treatment of them, which may or may not be correct and reflective of the actual data types. – EdStevens Feb 26 '20 at 19:00

Subtracting Dates ORA 01722

3 Answers3