CODEWITHSUNDEEP
sqloracle-database
Skn
Skn

Reputation: 101

Date calculations in Oracle SQL

I have a very basic question reg date calculations in Oracle SQL.

I have been using the following logic to calculate the age and identify records with age>=75:

(Date1 - Date2) / 365.25 >= 75

However the above logic was calculating the age incorrectly for the following scenario:

Date1 = MM-DD-2016
Date2 = MM-DD-1947

Although, here the age is 69, it was showing up in my output. After I added to_date for both the dates, the query did not bring this record.

My question is: I have not used to_date earlier in this query and was working for the other records fine, but why has it failed for this scenario?

Edit: I wanted to add, looks like my database records values as below when partial dates are entered: Date1 is 6/15/1947 Date2 is 6/15/2016

Upvotes: 0

Views: 1931

Answers (2)

Alex Poole
Alex Poole

Reputation: 191275

The only way i can see you'd get that result is if you have a date stored as -1947 instead of 1947:

with t (Date1, Date2) as (
  select date '2016-06-15', add_months(date '2016-06-15', -12*level)
  from dual
  connect by level <= 80
  union all
  select date '2016-06-15', date '-1947-06-15'
  from dual
)
select * from t
where (Date1 - Date2) / 365.25 >= 75;

DATE1      DATE2    
---------- ----------
06/15/2016 06/15/1941
06/15/2016 06/15/1940
06/15/2016 06/15/1939
06/15/2016 06/15/1938
06/15/2016 06/15/1937
06/15/2016 06/15/1936
06/15/2016 06/15/1947

That last record looks like it shouldn't be there. But my NLS_DATE_FORMAT mask is MM/DD/YYYY (to match what you're seeing); if I make it show the sign of the year it makes more sense:

alter session set nls_date_format = 'MM/DD/SYYYY';

with t (Date1, Date2) as (
  select date '2016-06-15', add_months(date '2016-06-15', -12*level)
  from dual
  connect by level <= 80
  union all
  select date '2016-06-15', date '-1947-06-15'
  from dual
)
select * from t
where (Date1 - Date2) / 365.25 >= 75;

DATE1         DATE2       
------------- -------------
6/15/ 2016    6/15/ 1941   
6/15/ 2016    6/15/ 1940   
6/15/ 2016    6/15/ 1939   
6/15/ 2016    6/15/ 1938   
6/15/ 2016    6/15/ 1937   
6/15/ 2016    6/15/ 1936   
6/15/ 2016    6/15/-1947

Or even better:

with t (Date1, Date2) as (
  select date '2016-06-15', add_months(date '2016-06-15', -12*level)
  from dual
  connect by level <= 80
  union all
  select date '2016-06-15', date '-1947-06-15'
  from dual
)
select to_char(date1, 'SYYYY-MM-DD') as date1, to_char(date2, 'SYYYY-MM-DD') as date2
from t
where (Date1 - Date2) / 365.25 >= 75;

DATE1       DATE2     
----------- -----------
 2016-06-15  1941-06-15
 2016-06-15  1940-06-15
 2016-06-15  1939-06-15
 2016-06-15  1938-06-15
 2016-06-15  1937-06-15
 2016-06-15  1936-06-15
 2016-06-15 -1947-06-15

So you can see the date from -1947 is shown, but the date from 1947 is not. And that is a lot more than 75 years ago:

select months_between(date '2016-06-15', date '-1947-06-15')/12 as years from dual;

     YEARS
----------
      3963

The reason adding to_date() around your values stopped the record appearing is that you would be implicitly converting the date to a string and then back to a date, using the same MM/DD/YYYY format mask. The intermediate string would not include the minus sign, so date '-1947-06-15' would become the string 06/15/1947. When that is converted back to a date the sign is still missing, and the string 06/15/1947 becomes date '1947-06-15'. Which is now only 69 years ago, not 3963 years ago.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use the add_months() function:

where date1 >= add_months(date2, 75*12)

The months calculations are more precise -- for years -- than the ones based on days.

Alternatively, you can use interval:

where date1 >= date2 + interval '75' year

Upvotes: 1

Related Questions