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

EOMONTH built-in function was introduced in SQL Server 2012, and it is used to get the last day of the month.

In earlier SQL Server versions, you had to calculate last day of the month using DATEADD and DATEDIFF functions like explained in previous blog posts:

Code to get the last day of the month in SQL Server

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

Here is an example how you can get the last day of the month you specify, using EOMONTH function:

DECLARE @Date1 datetime
SET @Date1 = '04/27/2014'
SELECT EOMONTH (@Date1)

eomonth_specified_date

To get the last day of the current month using EOMONTH function:

SELECT EOMONTH (GETDATE())

To get the last day of the previous month specify offset -1:

SELECT EOMONTH (GETDATE(), -1)

To get the last day of the next month specify offset 1:

SELECT EOMONTH (GETDATE(), 1)

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/2014'
SELECT DATEADD(DAY, 1, EOMONTH(@Date1, -1))

eomonth_specified_date_first_day

If the post helped you, please share it:
Pin It

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">