[email protected]      beta
Donate

How to Find Last Date of Month in SQL Server [ EOMONTH ]

SQL Server gets the last date of the month: Here in this article, we learn how to get the end date of any given month. Let assume one has the requirement to find start date and end date of the current month in SQL Server. In short, we need to get First Day of the current month or previous month, or Last day ( end date) of the current month or any given month in SQL server.

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

Output:

 SQL Server 2012 Get last date on CURRENT MONTH

 

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

Output:

SQL Server 2012 Get last date on NEXT MONTH

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

Output:

SQL Server 2012 Get last date on PREVIOUS MONTH

 


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).

Hope you like this tutorial. If you have any recommendations, please let us know what you think in the comment section below! See you again next time!

Share:    
Satinder Singh

Hi all, am Founder and Author of Codepedia.info, also atypical polyglot programmer from Bombay, India. I have a passion to create, solve, and deploy software applications.
       ForEach ( minute in MyLife ) { myExperience++ ; }



Post Comment


Your email address will not be published. Required fields are marked *