As a SQL Server developer, you may often come across questions related to date manipulation. One such popular question is how to find the last day of any month. In this article, we will explore different methods to achieve this.

Before we dive into the solutions, let’s understand the scenario. Suppose you have a requirement to find the last day of the previous month, the current month, or the next month. Additionally, you may also need to find the last day of a specific month and year combination. Let’s explore each of these scenarios.

Finding the Last Day of the Previous, Current, and Next Month

To find the last day of the previous month, you can use the following script:

SELECT DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE()), 0)) AS LastDay_PreviousMonth

To find the last day of the current month, you can use the following script:

SELECT DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE())+1, 0)) AS LastDay_CurrentMonth

To find the last day of the next month, you can use the following script:

SELECT DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, GETDATE())+2, 0)) AS LastDay_NextMonth

The result of executing these scripts will give you the respective last days of the previous, current, and next month.

Finding the Last Day of Any Month and Year

If you want to find the last day of a specific month and year combination, you can use the following script:

DECLARE @dtDate DATETIME
SET @dtDate = '8/18/2007'
SELECT DATEADD(s, -1, DATEADD(mm, DATEDIFF(m, 0, @dtDate)+1, 0)) AS LastDay_AnyMonth

In this script, you need to specify the desired month and year combination by setting the value of the @dtDate variable. The result will give you the last day of the specified month and year.

These methods provide a simple and efficient way to find the last day of any month in SQL Server. You can use them in your queries or stored procedures to handle date-related calculations.

Reference: Pinal Dave (https://blog.sqlauthority.com)