How to calculate the date difference in years and months in SQL Server?

Question

I am trying to calculate frequency estimate in SQL Server and I am using the following queries to get the results,

FORMAT(DATEADD(month,-66, DATEADD(dd, 0,DATEADD(qq, DATEDIFF(qq, 0, getdate()), 0))), 'd','us') --For -5.5 years Frequency

FORMAT(DATEADD(month,-6, DATEADD(dd, -1, DATEADD(qq, DATEDIFF(qq, 0, DATA_ASOF_TSTMP), 0))), 'd','us') --For -6 Months Frequency

I got the following results

5.5_Years_Frequency    6_Months_Frequency
          4/1/2015             3/30/2020

For -6 months frequency I got the end date as 3/30/2020, but March has 31 days, so, I want the result as 3/31/2020 instead of 3/30/2020 as shown below.

Expected Results,

5.5_Years_Frequency    6_Months_Frequency
          4/1/2015              3/31/2020

Can any one please help me to get the expected results?

Answer 1

Change the calculation order:

1. Determine the quarter
2. Subtract 6 months
3. Subtract 1 day

For 6 months frequency:

select FORMAT(DATEADD(dd, -1, DATEADD(month,-6, DATEADD(qq, DATEDIFF(qq, 0, getdate()), 0))), 'd','us');

Fiddle