;

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:

Syntax - EOMONTH Function in SQL Server
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:

Example - To get the last day of the month of a given date using EOMONTH
DECLARE @Date1 datetime;
SET @Date1 = '04/27/2019';
SELECT EOMONTH (@Date1) as 'Last Day of Month';
Output

Last Day of Month
-----------------------
2019-04-30

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

Example - To get the last day of the current month using the EOMONTH function
SELECT EOMONTH(GETDATE()) as 'Last Day of Current Month';
Output

Last Day of Current Month
---------------------------------
2019-04-30

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

Example - To get the last day of the previous month specifies offset value -1
SELECT EOMONTH(GETDATE(), -1) as 'Last Day of Previous Month';
Output

Last Day of Previous Month
----------------------------------
2019-03-31

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

Example - To get the last day of the next month specifies offset value 1
SELECT EOMONTH(GETDATE(), 1) as 'Last Day of Next Month';
Output

Last Day of Next Month
-----------------------------
2019-05-31

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:

Example - EOMONTH function can also be used to calculate the first day of the month
DECLARE @Date1 datetime;
SET @Date1 = '04/27/2019';
SELECT DATEADD(DAY, 1, EOMONTH(@Date1, -1)) as First Day of the Month';
Output

First Day of the Month
----------------------------
2019-04-01

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



Comments

Recent Posts
Tags