Get first and last day of month in SQL 2012 and SQL 2008 and earlier

This article has been reviewed, improved and moved to my new blog http://blog.hubacek.uk/get-first-and-last-day-of-month-in-sql-2012-and-sql-2008-and-earlier/

Today I had to create an ETL for batch loading of records into data warehouse for the current month. For this I had to get first and last day of the current month.

Today, I would like to share two approaches with you. First approach is for Microsoft SQL Server 2012 and newer with usage of EOMONTH function and second for earlier releases of SQL Server with DATEADD function.

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

Commands above might be hard to understand for the ones who are not so familiar with T-SQL yet; so I will try to explain:

Today’s date is 24th July

First day of month

-DAY(@today) + 1

You get the first day of month with DAY function (represented by integer) [24], make it negative [-24], add 1 [-23]

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

Last day of month

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

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

What we get is DATEADD(dd, -24, DATEADD(mm, 1, @today))

DATEADD(mm, 1, @today)

Add 1 month to current date [24th August]

We get 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 = GETDATE()

--First day of month

SELECT DATEADD(dd, -DAY(@today) + 1, @today)

--or

SELECT DATEADD(dd, 1, EOMONTH(@today, -1))

--Last day of month

SELECT EOMONTH(@today)

First day of month

EOMONTH(@today, -1)

Get last date of previous month [30st June]

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

Add one day => 1st July

Query to get first day of month is the same as for SQL 2008 R2 and earlier. Optionally you can use second query which combines EOMONTH and DATEADD functions.

Last day of month

Quite simple, huh? J

Conclusion

To save some work in future you can create your own function. Here follows example of my own BOMonth function:

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:        Jiri Hubacek

-- Create date: 24th July 2012

-- Description:    Function returns first day of month [date]

-- =============================================

CREATE FUNCTION BOMonth ( @dateIN DATE )

RETURNS DATE

AS

BEGIN

DECLARE @Result DATE

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

END

GO

And call it in your query:

SELECT dbo.BOMonth(GETDATE())

Have a nice weekend!

About these ads

About Jiří Hubáček

Experienced consultant and blogger for Microsoft SQL Server and related products. Likes cats, workouts and delicious food. Enjoys sailing, traveling, time spent with wife and friends.
This entry was posted in Microsoft SQL Server and tagged , , , , , , , , , , . Bookmark the permalink.

4 Responses to Get first and last day of month in SQL 2012 and SQL 2008 and earlier

  1. dan says:

    Great post.. How could you go about getting the 2nd last business Day of the current month ?

    • Hey Dan,
      thank you. You basically have two options.
      1. take advantage of your custom calendar. Sometimes referred as time dimension. Basically you would SELECT from the table with a WHERE clause.
      2. With the CASE statement. I don’t have a SSMS installed on this notebook so I might have some syntax error but generally it should give you the clue how to fulfill your requirements.


      CASE
      WHEN DATEPART(dw,EOMONTH(@today)) = 1 THEN @last_but_one_working_day = DATEADD(d, -3, EOMONTH(@today)) /* when last day is Sunday subtract three days */
      WHEN DATEPART(dw,EOMONTH(@today)) = 2 THEN @last_but_one_working_day = DATEADD(d, -3, EOMONTH(@today)) /* when last day is Monday subtract three days */
      WHEN DATEPART(dw,EOMONTH(@today)) = 3 THEN @last_but_one_working_day = DATEADD(d, -1, EOMONTH(@today)) /* when last day is Tuesday we are fine with Monday so we subtract one day */
      /* ... */
      END;

  2. sql dev says:

    The month after July is August. The last day of June is 30th. You may want to correct your examples.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s