How to Get First and Last Day of a Month in SQL Server using EOMONTH Function

Tutorialsrack 29/04/2019 SQL SQL Server

In this article, we will learn How to Get First and Last Day of a Month in SQL Server using EOMONTH Function.

EOMONTH Function in SQL Server

EOMONTH function is a built-in function was introduced in SQL Server 2012, and this function is used to get the last day of the month of a specified date, with an optional offset. This function allows you to add a second argument (which is optional) to move forward and backward.

Syntax

The basic syntax of SQL Server EOMONTH function is as shown below:

EOMONTH ( start_date , month_to_add );

Where,

start_date: A date expression that specifies the date for which to return the last day of the month.
month_to_add: An optional integer expression that specifies the number of months to add to start_date.

• If you specify the positive integer value as the second argument (Number), it will move forward to the specified number of months and display the last date of that month.
• If you specify the negative integer value as the second argument (Number), it will move backward to the specified number of months and display the last date of that month.

If this addition overflows the valid range of dates, then EOMONTH will raise an error.

Example

To get the last day of the month of a given date, a query is as follow:

DECLARE @Date1 datetime;
SET @Date1 = '04/27/2019';
SELECT EOMONTH (@Date1) as 'Last Day of Month';

To get the last day of the current month using the EOMONTH function, a query is as follow:

SELECT EOMONTH(GETDATE()) as 'Last Day of Current Month';

To get the last day of the previous month specifies offset value -1, a query is as follow:

SELECT EOMONTH(GETDATE(), -1) as 'Last Day of Previous Month';

To get the last day of the next month specifies offset value 1, a query is as follow:

SELECT EOMONTH(GETDATE(), 1) as 'Last Day of Next Month';

To Get the First Day of the month using EOMONTH Function:

EOMONTH function can also be used to calculate the first day of the month. Here is an example:

DECLARE @Date1 datetime;
SET @Date1 = '04/27/2019';
SELECT DATEADD(DAY, 1, EOMONTH(@Date1, -1)) as First Day of the Month';

I hope this article will help you to understand how to Get First and Last Day of a Month in SQL Server using EOMONTH Function.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Related Posts