Working with date is very interesting, I have seen people add too much code to get desired result while it can be achievable with simple logic. Below sample is to find whether the given date is end date of the month,
You can use the dateadd and day functions. The idea is you should add 1 day to the given date and check whether the result date’s day is 1 or not. If the result date’s day is one then the given date is end date of the month otherwise it is not the end date of the month.
SELECT CASE WHEN DAY(DATEADD(D,1,GETDATE()))=1 THEN 'MONTH END' ELSE 'NOT MONTH END' END AS PERIOD