Month Name and Last date of each month for previous 3 months in sql

I’m analyzing customer transactions for the previous 3 months, excluding the current month, I would like to get the month names of the previous 3 months and the last date of each of the 3 months.

WITH R(N) AS (
    SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N < 3
)
SELECT 
    LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month], 
    DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS [year]
FROM R;

—output

month   year
Aug     2019
Jul     2019
Jun     2019

—expected

month EOM_Date    year
Aug   2019-08-31  2019
Jul   2019-07-31  2019
Jun   2019-06-30  2019

Answer

You can use SQL Server EOMONTH() function to compute the last day of a month.

Consider:

WITH R(N) AS (
    SELECT 1 UNION ALL SELECT N+1 FROM R WHERE N < 3
)
SELECT 
    LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month], 
    EOMONTH(GETDATE(), -N) AS [EOM_DATE],
    DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) AS [year]
FROM R;