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