If you are using SQL server 2012 or above, then there is a built-in method EOMONTH which display the last date on the specified date.
What is an EOMONTH function?
The EOMONTH function returns the last date of the month that contains the specified date, with an optional offset. In short with this function you can get the last day of given date or current date. This function supports in SQL Server 2012 or higher version. I.e., it will not work in SQL Server 2008, SQL Server 2005.
Syntax : = EOMONTH(start_date, months)
EOMONTH takes 2 parameters, first as StartDate and next as month travels from that start date, and then outputs the last day of that month.
If we set parameter months as 0, then EOMONTH will output the last day of the month in which StartDate falls.
Query to find the Month End Date in SQL Server
1) To find the last date of the current month using EOMONTH
Here we set month as 0 which gives the current month Last Date in SQL Server
DECLARE @current_date DATE = GETDATE() SELECT EOMONTH (@current_date, 0) AS LastDayOfCurrentMonth
Another way to get the last date of the current month is without adding the parameter as written below.
DECLARE @current_date DATE = GETDATE() SELECT EOMONTH (@current_date) AS LastDayOfCurrentMonth
2) To find last day of next month using EOMONTH
Here we set month parameter as 1 which gives last day of next month in SQL Server 2012.
DECLARE @current_date DATE = GETDATE() SELECT EOMONTH (@current_date, 1 ) AS LastDayOfNextMonth
Also Read: How to Get Time part from Datetime
3) To find last day of previous month using EOMONTH
Here we set month parameter as -1, and this gives previous month last day in SQL Server 2012.
DECLARE @current_date DATE = GETDATE() SELECT EOMONTH (@current_date, -1 ) AS LastDayOfPreviousMonth
Find Last day in SQL server for older version ( SQL server 2005, SQL server 2008)
-- OLD Version DECLARE @dt DATE SET @dt = GETDATE() SELECT CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dt)+1,0)) AS DATE)as LastDayOfCurrentMonth
Conclusion: By using EOMONTH in SQL Server 2012 or above we can get month start date and end date. As here we learned how to show Current Month end date, Next Month end date or Previous month end date. We also did same for SQL server lower version .i.e (SQL SEVRVER 2005 / SQL SERVER 2008).