How to find whether the date is end date of the month in SQL

To know the given date is end date of the month, You can use the dateadd and day functions. that 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