oracle year calculation 1

[FALCONSEYE]

I am still learning Oracle. I am trying to determine if a given date is less than 2 years from sysdate. Is this a valid statement or is there a better way of doing this?

select (to_date(sysdate) - to_date('01/01/2008','dd/mm/yyyy'))/365 yearsBetween from dual;


thanks

http://www.fuzzysiberians.com

 

[Turkbear]

Hi,
If for use in a where clause, then try:

where Table.DateField < (Sysdate - 730.5)

(Assumes an actual Date type field in the database, otherise use to_date(Table.DateField) -
Note: Sysdate is already in Date format, so the function is not needed.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[carp]

That is close, provided you can afford to ignore leap years. Another approach might be something like

SELECT months_between(SYSDATE, your_date_here)/12 
    AS yearsBetween 
  FROM dual;

 

[carp]

And - since you are learning Oracle - here is a different approach that may introduce you to several new useful concepts:

SELECT DECODE(SIGN(SYSDATE + INTERVAL '2' YEAR - your_date_here), 
              1, 'Less than 2 years', 
              0, 'Exactly 2 years', 
             -1, 'More than 2 years') AS relative_to_2_years
  FROM dual;

Note that this assumes that you are only looking at dates in the future. If you are looking for dates within two years before or after the current date, we will need to change the queries a bit.

 

[FALCONSEYE]

good stuff, thanks. Carp, I get an error saying:


ORA-00932: inconsistent datatypes: expected NUMBER got DATE

i replaced your_date_here with '15-aug-2005'.

http://www.fuzzysiberians.com

 

[carp]

OK, another thing about Oracle is that sometimes you have to tell it you're passing in a date! Try

SELECT DECODE(SIGN(SYSDATE + INTERVAL '2' YEAR - TO_DATE('15-aug-2005','DD-mon-YYYY') ),
               1, 'Less than 2 years',
               0, 'Exactly 2 years',
              -1, 'More than 2 years') AS relative_to_2_years
   FROM dual;

 

[carp]

Since it appears you do want to include dates from the past, here is one that exposes yet another function:

   SELECT DECODE(SIGN((SYSDATE + ABS(SYSDATE - TO_DATE('15-aug-2005','DD-mon-YYYY')))-(SYSDATE + INTERVAL '2' YEAR)),
                 1, 'More Than 2 Years',
                 0, 'Exactly 2 Years',
                 -1, 'Less Than 2 Years') FROM dual;

 

[FALCONSEYE]

sweet, great help. thanks.

http://www.fuzzysiberians.com

 

[SantaMufasa]

FalconsEye,

I agree...Excellent help from both Turkbear and Carp...They deserve "Thank-you"

s, don't you agree?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[karluk]

I haven't tested all of carp's suggestions, but the last one fails an obvious boundary condition test. Specifically, it flags 18-DEC-2007 as being more than two years before today, 17-DEC-2009, even though it won't be a full two years until the stroke of midnight. This appears to be because he is comparing an interval two years in the future with an interval two years in the past without dealing with the fact that one interval will include a leap year while the other one does not.

SELECT DECODE(SIGN((SYSDATE + ABS(SYSDATE - TO_DATE('18-dec-2007','DD-mon-YYYY')))-(SYSDATE + INTERVAL '2' YEAR)),
                 1, 'More Than 2 Years',
                 0, 'Exactly 2 Years',
                 -1, 'Less Than 2 Years') FROM dual

SQL> /

DECODE(SIGN((SYSD
-----------------
More Than 2 Years

 

[karluk]

Carp's second last suggestion seems to work correctly on dates in the future. Here is an attempt to modify the logic so that it will work with both past and future dates.

  1  SELECT DECODE(SIGN(LEAST(SYSDATE, TO_DATE('17-dec-2011','DD-mon-YYYY')) + interval '2' year -
  2         GREATEST(SYSDATE, TO_DATE('17-dec-2011','DD-mon-YYYY'))),
  3                   1, 'Less Than 2 Years',
  4                   0, 'Exactly 2 Years',
  5                   -1, 'More Than 2 Years')
  6*    FROM dual
SQL> /

DECODE(SIGN(LEAST
-----------------
Less Than 2 Years



  1  SELECT DECODE(SIGN(LEAST(SYSDATE, TO_DATE('18-dec-2011','DD-mon-YYYY')) + interval '2' year -
  2         GREATEST(SYSDATE, TO_DATE('18-dec-2011','DD-mon-YYYY'))),
  3                   1, 'Less Than 2 Years',
  4                   0, 'Exactly 2 Years',
  5                   -1, 'More Than 2 Years')
  6*    FROM dual
SQL> /

DECODE(SIGN(LEAST
-----------------
More Than 2 Years

So it handles the boundary conditions correctly with future dates.

For dates in the past, the boundary conditions also work correctly:

  1  SELECT DECODE(SIGN(LEAST(SYSDATE, TO_DATE('17-dec-2007','DD-mon-YYYY')) + interval '2' year -
  2         GREATEST(SYSDATE, TO_DATE('17-dec-2007','DD-mon-YYYY'))),
  3                   1, 'Less Than 2 Years',
  4                   0, 'Exactly 2 Years',
  5                   -1, 'More Than 2 Years')
  6*    FROM dual
SQL> /

DECODE(SIGN(LEAST
-----------------
More Than 2 Years


  1  SELECT DECODE(SIGN(LEAST(SYSDATE, TO_DATE('18-dec-2007','DD-mon-YYYY')) + interval '2' year -
  2         GREATEST(SYSDATE, TO_DATE('18-dec-2007','DD-mon-YYYY'))),
  3                   1, 'Less Than 2 Years',
  4                   0, 'Exactly 2 Years',
  5                   -1, 'More Than 2 Years')
  6*    FROM dual
SQL> /

DECODE(SIGN(LEAST
-----------------
Less Than 2 Years

 

[carp]

Karluk -
Thank you for the follow-on analysis, which exposes an interesting facet. I was under the impression that Oracle's INTERVAL implementation took leap years into account (and indeed it may - I may just be hosing this up). I'll need to play around with this some more tomorrow to see what other incorrect notions I may be carrying. Do have a star!

 

[carp]

I need to do some more experimentation, but here's what I've noticed about INTERVAL:
The actual days between now and the same time on 12/19/2007 is

  1* select sysdate - to_date('19-DEC-2007','DD-MON-YYYY') + (sysdate - trunc(sysdate)) FROM DUAL;
730.52299

Yet when using INTERVAL, I get:

select sysdate - (sysdate - INTERVAL '2' YEAR) from dual;
731

So it appears that INTERVAL may be doing some rounding. I'll check the documentation to see if this is mentioned.

 

[carp]

The good thing about SQL is there is usually more than one way to skin any given cat:

SELECT CASE
          WHEN to_date('19-DEC-2007','DD-MON-YYYY') BETWEEN SYSDATE - INTERVAL '2' YEAR AND SYSDATE + INTERVAL '2' YEAR THEN
             'WITHIN 2 YEARS'
          ELSE 
             'NOT WITHIN 2 YEARS'
          END AS determination 
  FROM dual;

Based on an error code I received at one point, it appears that INTERVAL 'x' YEAR is calculating the number of Julian dates within the range (in this case, 731) and applying that number to the reference date (sysdate) to derive a new date. The good news is that it does account for leap years. The bad news is it forces the programmer to be aware of the fact that a leap year within the past 2 years does not necessarily mean there will be another one within the next 2 years!