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

  1. Nafi
    28.05.2012 04:55 | #1

    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
    01.06.2012 08:25 | #2

    I found these queries very helpful and thanks for sharing.

  3. roshan
    23.08.2012 06:27 | #3

    awesome dude… its realy helpfull t m… thnx

  4. Qasim
    08.09.2012 01:53 | #4

    GREAT HELP. THANKS MAN

  5. Alex
    28.09.2012 05:45 | #5

    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.

  6. Gijs
    07.06.2013 02:21 | #6

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

  7. rosi sreenivasa reddy
    16.07.2013 10:22 | #7

    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
    .

    .

    .
    ..
    .
    .
    .
    ..
    .
    .
    .

  8. Rakesh Fhaujdar
    28.07.2013 04:17 | #8

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

  9. Angel
  10. Nihar Panda
    03.01.2014 05:16 | #10

    Very helpful, comes handy

  11. Amy
    04.06.2014 12:37 | #11

    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!

  12. Johnson Bernad
    06.05.2015 05:48 | #12

    One of the best examples ….

  1. No trackbacks yet.