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:
,(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
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?