T-SQL Tips: Beginning of Month Calculation

One of the most frequently used date manipulations in SQL Server is for calculating the beginning and end dates of a month. This is particularly useful in date range filters and monthly aggregation calculations.
 SQL 2012 has brought in a new function EOMONTH to get end of month date directly without any manipulations. But it doesnt have a corresponding BOMONTH function to get month beginning date. This blog discusses some methods by which you can calculate the beginning date of a month on passing any date within it.
See illustration below

DECLARE @Date datetime = '20130520'

-- Using EOMONTH function
SELECT CAST(EOMONTH(@Date,-1) AS datetime)+1

--Using conventional DATEADD DATEDIFF combo
SELECT DATEADD(mm,DATEDIFF(mm,0,@Date),0)

--Using DAY function
SELECT @date - DAY(@date) + 1

The output is as given below

But EOMONTH has a second argument which helps us to find end of month date of any month relative to passed date. 

To get corresponding functionality for beginning of month date we can extend our first solutions as below

DECLARE @Date datetime = '20130520',@Month int=-2

-- Using EOMONTH function

SELECT CAST(EOMONTH(@Date,@Month-1) AS datetime)+1

--Using conventional DATEADD DATEDIFF combo

SELECT DATEADD(mm,DATEDIFF(mm,0,@Date)+ @Month,0)

--Using DAY function

SELECT DATEADD(mm,@Month,@date - DAY(@date) + 1 )

In above i've passed -2 as month offset to get beginning month date for the month lagging the passed date month by 2. The output will be as below which is as expected.

You may make these logic into a UDF  if you need to reuse it a lot as below

CREATE FUNCTION BOMONTH

(

@Date datetime,

@MonthOffset int

)

RETURNS datetime

AS

BEGIN 

DECLARE @ReturnDate datetime

SET @ReturnDate = CAST(EOMONTH(@Date,@MonthOffset-1) AS datetime)+1

RETURN (@ReturnDate)

END

And then call it like

SELECT dbo.BOMONTH('20130723',-3)

to get output as shown below

2013-04-01 00:00:00.000

You may use any of the logic given above for the UDF based on your SQL Server version.

Incidentally this was my 100th blog so thanks for all who encouraged me all through and made this possible. As always please revert with your comments and any further clarification as they've been my greatest motivator always!!