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

To get the first day of the previous month in SQL Server, use the following code:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

To get the last day of the previous month:

SELECT DATEADD(DAY, -(DAY(GETDATE())), GETDATE())

To get the first day of the current month:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

To get the last day of the current month:

SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

 

To get the first day of the next month:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)

To get the last day of the next month:

SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 2, 0))

In SQL Server 2012 and later you can use EOMONTH Function 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)

 

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))