SQL to find first and last day of a month

How to get the first and last day of any month.

To get the first and last day of any month when given a date we can use the date functions
as below.

Suppose we want the first and last day for the date '04/28/2009', that is for April 2009.

declare @given_date datetime
select @given_date = '04/28/2009'

-- SQL to get the first day of the month
select dateadd(dd,-(day(dateadd(mm,1,@given_date))-1),dateadd(mm,0,@given_date))

-- SQL to get the last day of the month
select dateadd(dd, -day(dateadd(mm,1,@given_date)), dateadd(mm,1,@given_date))

To get next or previous month's first or last day change the highlighted number accordingly.



--SQL to get the first day of the second next month
select dateadd(dd,-(day(dateadd(mm,1,@given_date))-1),dateadd(mm,2,@given_date))

-- SQL to get the last day of the second next month
select dateadd(dd, -day(dateadd(mm,1,@given_date)), dateadd(mm,3,@given_date))