Oracle sql difference between dates

Hi Experts,
how do I get the number of days between two dates. I have 3 columns in my table. two of them have dates.
I have to set the third one 'Y' or 'N'
'Y' if the number of days between the two date columns is = 3 years and 'N' if it is more than 3 years.

the values I have in the two timestamp columns are like below

column1                                                         column2
___________________________________________________________
23-JAN-05 12.00.00.000000000 AM      21-JAN-15 12.00.00.000000000 AM
23-JAN-05 12.00.00.000000000 AM      21-JAN-15 12.00.00.000000000 AM
09-JAN-15 12.00.00.000000000 AM      08-JAN-15 12.00.00.000000000 AM
20-JAN-15 12.00.00.000000000 AM      18-JAN-25 12.00.00.000000000 AM
05-JAN-14 12.00.00.000000000 AM      05-JAN-17 12.00.00.000000000 AM

so I need to set the third column to 'Y' only for the last record in the above example since the difference between column2 and column1 is 3 years.
smaligAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongCommented:
I assume if it's less than 3 years it would show as "Y" as well....

you can try use months_between function to get the month difference and then do necessary comparison for years in Oracle, like:
select 
column1, column2,
case
   when abs(months_between(column1, column2)) /12 <= 3 then 'Y'
   else 'N' end Column3
from yourtable

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ryan ChongCommented:
or
select 
column1, column2,
case
   when abs(months_between(column1, column2))  <= 36 then 'Y'
   else 'N' end Column3
from yourtable

Open in new window

0
ThommyCommented:
You can simply calculate the difference between 2 Oracle dates in days by subtraction:

SELECT abs(trunc(sysdate - TO_DATE('24/09/2014', 'dd/mm/yyyy'))) DAYS FROM DUAL
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

johnsoneSenior Oracle DBACommented:
Since these are timestamps, why are we trying to do date math?  Do timestamp math.  That is the datatype you have.
WITH mytab (column1, column2) 
     AS (SELECT To_timestamp('23-JAN-05 12.00.00.000000000 AM', 
                    'dd-mon-yy hh:mi:ss.ff am'), 
                To_timestamp('21-JAN-15 12.00.00.000000000 AM', 
                'dd-mon-yy hh:mi:ss.ff am') 
         FROM   dual 
         UNION ALL 
         SELECT To_timestamp('23-JAN-05 12.00.00.000000000 AM', 
                'dd-mon-yy hh:mi:ss.ff am'), 
                To_timestamp('21-JAN-15 12.00.00.000000000 AM', 
                'dd-mon-yy hh:mi:ss.ff am') 
         FROM   dual 
         UNION ALL 
         SELECT To_timestamp('09-JAN-15 12.00.00.000000000 AM', 
                'dd-mon-yy hh:mi:ss.ff am'), 
                To_timestamp('08-JAN-15 12.00.00.000000000 AM', 
                'dd-mon-yy hh:mi:ss.ff am') 
         FROM   dual 
         UNION ALL 
         SELECT To_timestamp('20-JAN-15 12.00.00.000000000 AM', 
                'dd-mon-yy hh:mi:ss.ff am'), 
                To_timestamp('18-JAN-25 12.00.00.000000000 AM', 
                'dd-mon-yy hh:mi:ss.ff am') 
         FROM   dual 
         UNION ALL 
         SELECT To_timestamp('05-JAN-14 12.00.00.000000000 AM', 
                'dd-mon-yy hh:mi:ss.ff am'), 
                To_timestamp('05-JAN-17 12.00.00.000000000 AM', 
                'dd-mon-yy hh:mi:ss.ff am') 
         FROM   dual) 
SELECT * 
FROM   mytab 
WHERE  CASE 
         WHEN column1 > column2 THEN column1 
         ELSE column2 
       END - To_yminterval('P3Y') > CASE 
                                      WHEN column1 > column2 THEN column2 
                                      ELSE column1 
                                    END; 

Open in new window

The case statement is to deal with not knowing which of the 2 columns is the from date and the to date.  From the sample data it seems that either one can be the either date.

As for an update statement, what I would do is set all of the values in the table to N with one blanket update.  Then use the where clause in the select statement to selectively set the rows that match to Y.
0
awking00Commented:
update table set column3 = case when abs(trunc(column2) - trunc(column1)) >= 1095 then 'N' else 'Y' end;
0
johnsoneSenior Oracle DBACommented:
1095 is not an accurate number of days.  What if there is a leap year in there?  Over a 3 year span, you are likely to have one.
0
awking00Commented:
johnsone, I agree that doesn't take into account the possibility that leap years would require a difference of 1096, but there is an easy workaround. The question that hasn't been fully answered is, "Is the requirement for an update to 'Y' that the dates be exactly equal to 3 years or can they be less than or equal?" If less than or equal to 3 years the following update statement should work:
update table set column3 = case when add_months(trunc(column1),36) >= trunc(column2) then 'Y' else 'N' end;
If exactly equal to 3 years then the following update statement should work:
update table set column3 = case when add_months(trunc(column1),36) = trunc(column2) then 'Y' else 'N' end;
0
smaligAuthor Commented:
sorry for the delay. the requirement is exactly 3 years.
0
awking00Commented:
Then the second update statement I showed should work.
0
johnsoneSenior Oracle DBACommented:
Updates using TRUNC wouldn't account for a time, so it wouldn't be exactly 3 years.  Also, updates using ADD_MONTHS wouldn't account for sub seconds as they only work with DATE datatypes and a DATE would be returned which would strip off sub seconds.  For a truly exactly 3 years, you need to use intervals.
0
awking00Commented:
I can't imagine there is a reason to account for time especially down to the sub-seconds to update what appears to be some kind of flag denoting whether or not it's been 3 years between dates. It also appears from the sample column data provided that the time stamp is always ending in 12.00.00.000000000 AM so there would be no need to account for a time portion.
0
johnsoneSenior Oracle DBACommented:
Just trying to point out that if you are looking for exactly 3 years, you cannot do it with date functions you have to do it with intervals.  All of the data in the table can be different from the sample data.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.