asked on

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.

ASKER CERTIFIED SOLUTION

Ryan Chong

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ryan Chong

select 
column1, column2,
case
   when abs(months_between(column1, column2))  <= 36 then 'Y'
   else 'N' end Column3
from yourtable

Open in new window

Thommy

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

johnsone

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.

awking00

update table set column3 = case when abs(trunc(column2) - trunc(column1)) >= 1095 then 'N' else 'Y' end;

johnsone

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.

awking00

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;

smalig

ASKER

sorry for the delay. the requirement is exactly 3 years.

awking00

Then the second update statement I showed should work.

johnsone

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.

awking00

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.

johnsone

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.