Get the first and last day of a month in SQL Server 2012 and 2008

Today, I had to create an ETL script for current’s month data loading into the data warehouse. In my script, I had to get the first and last day of the current month.

I’m going to demonstrate two approaches. First approach is for Microsoft SQL Server 2012 and newer with EOMONTH function and second for earlier releases with DATEADD function.

Pre SQL Server 2012 approach with DATEADD:

--Today's date is 24th July 2012
DECLARE @today AS DATE = '2012-08-24';

--First day of month
SELECT DATEADD(dd, -DAY(@today) + 1, @today);

--Last day of month
SELECT DATEADD(dd, -DAY(DATEADD(mm, 1, @today)), DATEADD(mm, 1, @today));

Command decomposition follows. Bellow example is assuming today’s date is 24^th July.

First day of a month

-DAY(@today) + 1

You get day of a month with DAY function [24], make it negative [-24] and add 1 [-23]. Use DATEADD function  for the date’s subtraction.

DATEADD(dd, -23, @today) -- => 24 – 23 = 1st July

Last day of a month

Add 1 month to the current date, get the current day of month [24] and make it negative [-24]

-DAY(DATEADD(mm, 1, @today))

What we get is

DATEADD(dd, -24, DATEADD(mm, 1, @today))

Add 1 month to current date [24^th August]

DATEADD(mm, 1, @today)

We get DATEADD(dd, -24, 2012-08-24 )) => 31^st July

DATEADD(dd, -24, 2012-08-24 ))// => 31st July

SQL 2012 approach with EOMONTH and little DATEADD

--Today's date is 24th July

DECLARE @today AS DATE = SYSDATETIME();

--First day of a month
SELECT DATEADD(dd, -DAY(@today) + 1, @today);
--or in another way
SELECT DATEADD(dd, 1, EOMONTH(@today, -1));

--Last day of a month
SELECT EOMONTH(@today);

First day of a month

EOMONTH function accepts two arguments. First the reference date,  second how many months to add to the reference date. We take advantage of this behaviour by getting the last day of previous month [30^st June].

EOMONTH(@today, -1)

By adding 1 day with DATEADD function we get 1^st July.

DATEADD(dd, 1, 2012-07-31)

Last day of month

It couldn’t be simpler 🙂

Conclusion

To save some work in the future you can create your own function. Hereafter follows my own function:

SET ANSI_NULLS ON
-- =============================================

-- Author: Jiri Hubacek
-- Create date: 24th July 2012
-- Description: Function returns first day of month [date]
-- =============================================

CREATE FUNCTION FirstDayOfMonth ( @dateIN DATE )
RETURNS DATE
AS
BEGIN
DECLARE @Result DATE;

SELECT @Result = DATEADD(dd, -DAY(@dateIN) + 1, @dateIN);
RETURN @Result;
END;

And call it in your query:

SELECT dbo.FirstDayOfMonth (SYSDATETIME());

Have a nice weekend!