Home > SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > How to get First and Last day of a month – TSQL

How to get First and Last day of a month – TSQL

Following queries can be used to get the first/last days of a month.

To get first day of a month use:

– © 2011 – Vishal (http://SqlAndMe.com)

 

– First Day Previous/Current/Next Months

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0),

            'First Day of Previous Month'

UNION ALL

SELECT      DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE())) - 1),

            DATEADD(MONTH, -1, GETDATE())),

            'First Day of Previous Month (2)'

UNION ALL

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),

            'First Day of Current Month'

UNION ALL

SELECT      DATEADD(DAY, -(DAY(GETDATE()) - 1), GETDATE()),

            'First Day of Current Month (2)'

UNION ALL

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0),

            'First Day of Next Month'

UNION ALL

SELECT      DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE())) - 1),

            DATEADD(MONTH, 1, GETDATE())),

            'First Day of Next Month (2)'

Result Set:

                       

———————– ——————————-

2011-04-01 00:00:00.000 First Day of Previous Month

2011-04-01 15:47:36.660 First Day of Previous Month (2)

2011-05-01 00:00:00.000 First Day of Current Month

2011-05-01 15:47:36.660 First Day of Current Month (2)

2011-06-01 00:00:00.000 First Day of Next Month

2011-06-01 15:47:36.660 First Day of Next Month (2)

 

(6 row(s) affected)

The above queries can be generalized as below:

DECLARE @DURATION INT = 2

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION, 0)

            AS '+2 Months'

 

SET @DURATION = -2

SELECT      DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION, 0)

            AS '-2 Months'

Result Set:

+2 Months

———————–

2011-07-01 00:00:00.000

 

(1 row(s) affected)

 

-2 Months

———————–

2011-03-01 00:00:00.000

 

(1 row(s) affected)

And, to get last day of a month use:

– © 2011 – Vishal (http://SqlAndMe.com)

 

– Last Day Previous/Current/Next Months

SELECT      DATEADD(DAY, -(DAY(GETDATE())), GETDATE()),

            'Last Day of Previous Month'

UNION ALL

SELECT      DATEADD(MILLISECOND, -3,

            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)),

            'Last Day of Previous Month (2)'

UNION ALL

SELECT      DATEADD(DAY, -(DAY(DATEADD(MONTH, 1, GETDATE()))),

            DATEADD(MONTH, 1, GETDATE())),

            'Last Day of Current Month'

UNION ALL

SELECT      DATEADD(MILLISECOND, -3,

            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)),

            'Last Day of Current Month (2)'

UNION ALL

SELECT      DATEADD(DAY, -(DAY(DATEADD(MONTH,0,GETDATE()))),

            DATEADD(MONTH, 2, GETDATE())),

            'Last Day of Next Month'

UNION ALL

SELECT      DATEADD(SECOND, -1,

            DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 2, 0)),

            'Last Day of Next Month (2)'

Result Set:

                       

———————– ——————————

2011-04-30 15:54:35.523 Last Day of Previous Month

2011-04-30 23:59:59.997 Last Day of Previous Month (2)

2011-05-31 15:54:35.523 Last Day of Current Month

2011-05-31 23:59:59.997 Last Day of Current Month (2)

2011-06-30 15:54:35.523 Last Day of Next Month

2011-06-30 23:59:59.000 Last Day of Next Month (2)

 

(6 row(s) affected)

The above queries can be generalized as below:

DECLARE @DURATION INT = 2

SELECT   DATEADD(MILLISECOND, -3,

         DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION + 1, 0))

         AS '+2 Months'

 

SET @DURATION = -2

SELECT   DATEADD(MILLISECOND, -3,

         DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + @DURATION + 1, 0))

         AS '-2 Months'

Result Set:

+2 Months

———————–

2011-07-31 23:59:59.997

 

(1 row(s) affected)

 

-2 Months

———————–

2011-03-31 23:59:59.997

 

(1 row(s) affected)

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. May 28, 2012 at 4:55 pm

    Good Queries.
    An extensive list of queries to find the start and end dates of any months is here:

    http://dotnet-programming-solutions.blogspot.com/2012/03/sql-server-find-first-day-last-day-of.html

    I think it helps.

  2. Marvin
    June 1, 2012 at 8:25 pm

    I found these queries very helpful and thanks for sharing.

    • roshan
      August 23, 2012 at 6:27 pm

      awesome dude… its realy helpfull t m… thnx

  3. September 8, 2012 at 1:53 am

    GREAT HELP. THANKS MAN

  4. Alex
    September 28, 2012 at 5:45 pm

    this is not working if you use 2012-07-31 and would like to get the end of august. try it. you will get 30th of august.

  5. Gijs
    June 7, 2013 at 2:21 pm

    This one is so elegant: DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
    There’s beauty in that query.

  6. rosi sreenivasa reddy
    July 16, 2013 at 10:22 am

    k u done well,
    but i need to print the 1st day of year to last date of the year individually
    ex:
    u take anything as columnname
    jan 1 2013
    jan 2 2013
    ….

    .
    .
    .

    .
    .
    .

    .

    .
    .

    feb 1 2013
    .

    .

    .
    ..
    .
    .
    .
    ..
    .
    .
    .

  7. Rakesh Fhaujdar
    October 28, 2013 at 4:17 pm

    select DATEADD(MM,datediff(month,0,getdate()),0) firstdayofmonth
    go
    select DATEADD(mm,datediff(month,0,getdate())+1,0)-1 lastdayofcurrentmonth

  8. Angel
  9. Nihar Panda
    January 3, 2014 at 5:16 pm

    Very helpful, comes handy

  10. Amy
    June 4, 2014 at 12:37 am

    a little error here, be careful!
    for example: if the current date is ‘2013-12-31′, the following sql will return ‘2014-01-28′
    SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH,0,GETDATE()))),
    DATEADD(MONTH, 2, GETDATE())),
    ‘Last Day of Next Month’

    The correction is, please use the same dateadd statement, see below:
    SELECT DATEADD(DAY, -(DAY(DATEADD(MONTH,2,GETDATE()))),
    DATEADD(MONTH, 2, GETDATE())),
    ‘Last Day of Next Month’

    Same as others.

    BTW, thank you for the post, which helped me a lot!

  1. October 15, 2011 at 1:37 pm
  2. July 15, 2013 at 10:04 am

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

Follow

Get every new post delivered to your Inbox.

Join 268 other followers

%d bloggers like this: