CODEWITHSUNDEEP
sqldateplsqloracle11gfortran
AJR
AJR

Reputation: 589

Subtract 2 dates PL/SQL but one of the dates is 0

I am converting some calculations from FORTRAN to PL/SQL. Please let me explain:

In my Oracle database table, I have the following date values for two of the attributes. 

attribute1: '01/01/0001', This is a date not a string.
attribute2: '08/24/1918', This is a date not a string.

What I am trying to do is, if attribute1 equal to '01/01/0001', then convert it to 00/00/0000 then minus attribute2 just like code below:

 //Decode missing for '01/01/0001'
 select round((to_date('01/01/0001', 'mm/dd/yyyy') - to_date('01/24/1918', 'mm/dd/yyyy'))/365.25, 3)as dt from dual;

What the FORTRAN code is doing is converting '01/01/0001' to '0/ 0/ 0' and then doing the minus. Like so,

 round((('0/ 0/   0' - '08/24/1918') / 365.25), 3). Round to the 3 decimal place.

I know I'm missing a decode in the PL/SQL code above to convert '01/01/0001' to zero basically.

Running the PL/SQL code, you will get a value of -1917.027. The FORTRAN code is returning a value of -1918.732. The PL/SQL code is off by a day, a month, and a year.

How can I convert '01/01/0001' to zero and then minus it by a date ??

Please help, and thanks in advance.

Upvotes: 2

Views: 190

Answers (1)

user918967
user918967

Reputation: 2167

Seems to me it would be easier to just use a CASE statement. Something like :

CASE 
  WHEN attribute1 = '01/01/0001' THEN -1*(EXTRACT(YYYY FROM attribute2)+ EXTRACT(DDD FROM attribute2)/365)
  ELSE attribute1 - attribute2
END

Alternatively, you could just convert everything to Julian dates and then do the subtraction with a correction factor for when the date was 01/01/0001

Upvotes: 1

Related Questions