How Can I Find the Last Day of a Month in SQL?

Efficiently Finding the Last Day of a Month in SQL

Calculating the last day of a month is a frequent requirement in SQL database management. This article explores efficient methods, particularly focusing on the EOMONTH function for its superior flexibility.

While methods like using DATEADD to find the first day of the next month work, they lack the elegance and directness of a dedicated solution.

Leveraging the EOMONTH Function (SQL Server 2012 and later)

The EOMONTH function offers a streamlined approach for determining the last day of any given month. It directly returns the last day, eliminating the need for complex calculations.

The function takes two arguments:

• start_date: The date from which to calculate the last day of its month.
• month_to_add (optional): An integer representing the number of months to add or subtract from the start_date. A positive value shifts the result forward, a negative value shifts it backward.

Example Usage

To obtain the last day of the month for a specific date, use the following:

<code class="language-sql">SELECT EOMONTH('2024-03-15')  -- Returns 2024-03-31</code>

To find the last day of the month two months from now, use:

<code class="language-sql">SELECT EOMONTH(GETDATE(), 2) -- Returns the last day of the month two months from the current date.</code>

Conclusion

For SQL Server 2012 and later versions, the EOMONTH function provides the most efficient and readable method for finding the last day of a month. Its optional second parameter adds significant flexibility for date-based calculations.

The above is the detailed content of How Can I Find the Last Day of a Month in SQL?. For more information, please follow other related articles on the PHP Chinese website!