Introduction
This article provides an explanation about finding the last day of any month based on the given date in the SQL server as well as we will learn about what is EOMONTH function as well as the syntax and use of this function in the SQL server.
In my previous article, I explained how to split comma-separated strings in SQL Server, how to create a missing index from the execution plan as well as information about the MERGE statement in SQL server, and in this article, I'll explain another useful function EOMONTH for fetch the last day of the current, next and previous month.
While we working with the data-driven application, sometimes we get such a requirement to find the last date of the current month, the last date of the previous month, or the last date of the next month based on the given requirement. Today I got the same requirement, a simple task is assigned to me to generate a movement report for the products, and for that, I need the last day of the current, next, and previous month based on the date parameter for setting a few conditions in the stored procedure of the movement report. So, today I'll show you how to find the last day of the current, next, and previous month in the SQL server.
If you are using SQL server version 2012 or above then you can use the EOMONTH function to get the last day of the current month, the last day of the next month, and the last day of the previous month.
Requirement
1) What is the EOMONTH function in SQL Server?
2) Syntex of EOMONTH function in SQL Server
3) Find the last day of the current, next, and previous month using the EOMONTH function in SQL Server.
4) Find the last day of the current, next, and previous month without the EOMONTH function in SQL Server.
Implementation
What is the EOMONTH function in SQL Server?
The EOMONTH function is a SQL Server function that is used to return the last date of the month based on the specified date with an optional offset.
NOTE: This function is only supported in the SQL server version 2012 or higher version of the SQL Server.
Syntex
EOMONTH ( start_date [, month_to_add ] )
As you can see in the syntax above EOMONTH takes 2 parameters as an argument, the first parameter as start_date and the second parameter that is optional as month travels, and accept integer expression as an argument that specifies the number of months to add to start_date.
Now, let's take a simple example and fetch the last date of the month based on the specified date
Example
The Last Date of The Previous Month
Fetch the last date of the previous month based on the specified date using the EOMONTH function in the SQL server.
DECLARE @date DATE = GETDATE() SELECT EOMONTH (@date, -1) AS LastDay_PreviousMonth
Fetch the last date of the previous month based on the specified date without the EOMONTH function in the SQL server.
DECLARE @date DATE = GETDATE() SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, @date),0)) AS LastDay_PreviousMonth
Output
The Last Date of The Current Month
Fetch the last date of the current month based on the specified date using the EOMONTH function in the SQL server.
DECLARE @date DATE = GETDATE() SELECT EOMONTH (@date, 1) AS LastDay_CurrentMonth
Fetch the last date of the current month based on the specified date without the EOMONTH function in the SQL server.
DECLARE @date DATE = GETDATE() SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, @date)+1,0)) AS LastDay_CurrentMonth
Output
The Last Date of The Next Month
Fetch the last date of the current month based on the specified date using the EOMONTH function in the SQL server.
DECLARE @date DATE = GETDATE() SELECT EOMONTH (@date, 0) AS LastDay_NextMonth
Fetch the last date of the current month based on the specified date without the EOMONTH function in the SQL server.
DECLARE @date DATE = GETDATE() SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0, @date)+2,0)) AS LastDay_NextMonth
Output
Summary
In this article, we learned how to get the last day of the current, next, and previous month based on the date specified using the EOMONTH function and without the use of the inbuilt function in the SQL server.
