EOMONTH Function in SQL Server

Whenever we want to calculate the last of a month, the EOMONTH function of SQL Server is used. An additional second parameter can also be passed to the EOMONTH function to add the month and display the last date.

Syntax of EOMONTH Function in SQL Server:

The syntax for EOMONTH Function in SQL Server is:

In the syntax written above:

• Starting_date: The starting_date is the critical parameter, the last date of the month of this date is displayed as an output of the EOMONTH function.
• Months_to_add: The months_to_add is the second parameter, the numeric value passed as a parameter will be added to the month of the date that is passed as the first parameter, and the last date of that month be displayed.

How to use EOMONTH Function in SQL Server?

Let us understand the working and usage of the EOMONTH Function in SQL Server with the help of an example. First, let us create a table named meetings having two columns, starting_date and meeting_date. This table will store the starting date of the month in the starting_date column, and the meeting date will be saved in the meeting_date column of the meetings table. The syntax for creating a table named meetings and having two columns named starting_date and meeting_date having datatype as DATE is:

As shown in the above image, a table named meetings with the schema above mentioned is created successfully.

Now, we will add data into the starting_date column of the meetings table, and the data in the meeting_date will be added automatically with the use of the EOMONTH function in SQL Server. The syntax for adding data to the starting_date column of the meetings table is:

As we can see in the above images, all the insert queries are executed successfully. Therefore, the data in the starting_date column is added successfully that is reflected as the output of the SELECT query.

We have added eight rows of data in the starting_date column, but we have not added any data in the meeting_date column. To add data to the meeting_date column, we will be using the EOMONTH function. The syntax for adding data in the meeting_date column using the EOMONTH function is:

As shown in the image, the data in the meeting_date column of the meetings table has been added successfully using the EOMNONTH function. The parameter passed as input to the EOMONTH function is the data present in the starting_date column of the meetings table. The EOMONTH function takes data in the starting_date column as input date and gets the last date of the month of the date given as input to the EOMONTH function and adds that last date to the meeting_date column.

We can also use the EOMONTH function to print the last date of the following month. For this, we need to pass a second argument to the EOMONTH function that will be used as an offset to the month of the date passed as the first parameter. The syntax for the same is:

As we can see in the image, the last of the following month is displayed successfully.

We can also print the number of days in a month using the EOMONTH function and the DAY function.

In order to display the number of days of a specified month, the following steps need to be followed:

• First, the EOMONTH() function is used to get the last day of the month.
• Then, the DAY() function is used to calculate the last day of the month.

The syntax for printing the number of days in a month is:

As shown in the image, the number of days in the month is displayed successfully.

So, this article helps us to understand the working of the EOMONTH Function in SQL Server and how to use it according to our problem requirement.