CODEWITHSUNDEEP
sqloracle-database
Jake Wagner
Jake Wagner

Reputation: 826

Subtracting Dates ORA 01722

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

Upvotes: 0

Views: 669

Answers (3)

Deepika
Deepika

Reputation: 296

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;

Upvotes: 1

Marmite Bomber
Marmite Bomber

Reputation: 21043

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)
   )

Upvotes: 2

OldProgrammer
OldProgrammer

Reputation: 12159

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.

Upvotes: 0

Related Questions