How to Find the Last Date of Any Month in SQL Server

watch_later Tuesday, December 17, 2019
comment 1 Comment

Introduction


This article provides an explanation about to find 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 syntax and use of this function in SQL server.

In my previous article, I explained how to split comma separated string 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
SQL Server - Last Date of Any Month

While we working with the data-driven application, sometimes we get such a requirement to find the last date of the current month, last date of the previous month or 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 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 EOMONTH function in SQL Server.
4)  Find the last day of the current, next and previous month without 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 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 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 Previous Month


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 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 Current Month


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

The Last Date of The Next Month

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 EOMONTH function and without use of inbuilt function in sql server.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

avatar

It is a very nice blog. Really it is a very international destination post. Thanks for sharing this post.
outsourcing training center in dhaka

delete December 26, 2019 at 6:38 AM



sentiment_satisfied Emoticon