Data Caclulation (Number of Full Months and remaining Days between two dates)

[maviolette]

I have a need to calculate the number of FULL Months and remaining days as two separate amounts.  The month is based on the day of the Month the date begins.  As an example 12/5/2013 to 1/4/2014 would be 1 month and 0 days.  Though 12/5/2013 to 1/6/2014 would be one month and 2 days.  Though 12/5/2013 to 1/3/2014 would be 0 months and 30 days.

Another good example is to use the dates of 2/20/2023 and 12/3/2002:  Want to calculate 242 months and 17 days.  I've been able to calculate this one accurately because the day from the larger date is also larger than the day of the earlier date.  This does NOT work well when the larger date has a smaller day number than the earlier date.  This produces a negative number of days.

The current SQL I'm using is as follows:

select a.cl_claim_number

,b.cl_disabled_date

,a.bd_begin_date

,a.bd_max_date

,(a.bd_max_date - a.bd_begin_date) month(4)

,extract (day from a.bd_max_date) - extract (day from a.bd_begin_date)

from polaris_p.bendates a

inner join polaris_p.claim b

on b.row_end_date is null

and b.cl_claim_number = a.cl_claim_number

and b.cl_terminate_date is null

where a.row_end_date is null

============================================

Example results are as follows:

1999-04-19     2018-08-08     232 months / -11 days     WANT 231 months / 19 days

2001-05-23     2016-05-19     180 months / - 4 days      WANT 179 months / 26 days

1998-08-02     2016-06-21     214 months / 19 days      OK

[dnoeth]

Why do you want 19 days for your first example and 26 for #2?

Based on the start date #2 should result in 27 days?

You can get the month with a CASE like

CASE WHEN EXTRACT (DAY FROM b)+1 >= EXTRACT (DAY FROM a)
     THEN (b-a MONTH(4))
     ELSE (b-a MONTH(4)) - INTERVAL '1' MONTH
END AS m

and another CASE with multiple WHENs including

ADD_MONTHS(b+1,-CAST(m AS INT)) - a

for the days.

But do you really need months and days?

One month can be between 28 and 31 days, so 1 month and 2 days could be less than 1 month.

What do you expect for following dates?

2013-01-28  2013-02-27  1-0?

2013-01-28  2013-02-28  1-1?

2013-01-29  2013-02-28  1-2?

2013-01-30  2013-02-28  1-3?

2013-01-31  2013-02-28  1-4?

2013-02-01  2013-03-01  1-1?

Why don't you go with weeks/days instead of months/days, imho most people prefer that?