How to Get the Last Day of Any Month in SQL?

Get the last day of the month in SQL

In database management, it is often necessary to operate on dates and extract specific information from them. A common task is to determine the last day of a given month. This guide explores how to achieve this in SQL for any arbitrary date.

If you know the first day of the month, add a month and subtract a day to get the last day, as shown in the query below:

<code class="language-sql">DATEADD(DAY, DATEADD(MONTH, '2009-05-01', 1), -1)</code>

However, to generalize this process to any given date, a more robust solution is required.

Using the EOMONTH function (SQL Server 2012 and later)

SQL Server 2012 and later introduces the EOMONTH function, which provides a convenient way to determine the last day of the month. The syntax is as follows:

<code class="language-sql">EOMONTH(start_date, [month_to_add])</code>

Among them:

• start_date is the date from which you want to calculate the last day of the month.
• month_to_add (optional) Specifies the month offset to add to start_date before finding the last day of the month.

Usage Example

To find the last day of the month for a given date, just use the EOMONTH function as follows:

<code class="language-sql">SELECT EOMONTH(@SomeGivenDate)</code>

This query will return the last day of the month containing the specified @SomeGivenDate.

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