How to Get First and Last Day of a Month in SQL Server

To get the first day of the previous month in SQL Server, use the following code:

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

first_day_of_last_month

To get the last day of the previous month:

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

last_day_of_last_month

To get the first day of the current month:

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

first_day_of_current_month

To get the last day of the current month:

SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

last_day_of_current_month

To get the first day of the next month:

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

first_day_of_next_month

To get the last day of the next month:

SELECT DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 2, 0))

last_day_of_next_month

In SQL Server 2012 and later you can use EOMONTH Function to Get First and Last Day of a Month in SQL Server:
EOMONTH Function to Get First and Last Day of a Month in SQL Server

Related blog post:
Code to get the last day of the month in SQL Server

If the post helped you, please share it:
Pin It

10 comments to How to Get First and Last Day of a Month in SQL Server

  • Alvin

    last day of the month..
    select to_number((TO_char( LAST_DAY(SYSDATE),'DD')/1)) from dual;

  • Aviv

    Great write up - many thanks!

  • anonymous

    Thanks for this great help.

  • Abubakar Arshad

    Great Work Man.

  • Anna

    First day and last day of previous one month:
    Dateadd(d,+1,EOMONTH(getdate(),-2))
    EOMONTH(getdate(),-1)

    First day and last day of previous two month:
    Dateadd(d,+1,EOMONTH(getdate(),-3))
    EOMONTH(getdate(),-2)

    First day and last day of previous one month:
    Dateadd(d,+1,EOMONTH(getdate(),-2))
    EOMONTH(getdate(),-1)

    First day and last day of current month:
    Dateadd(d,+1,EOMONTH(getdate(),-1))
    EOMONTH(getdate())

    First day and last day of previous one month:
    Dateadd(d,+1,EOMONTH(getdate(),-2))
    EOMONTH(getdate(),-1)

    First day and last day of next month:
    Dateadd(d,+1,EOMONTH(getdate()))
    EOMONTH(getdate(),+1)

    First day and last day of next two month:
    Dateadd(d,+1,EOMONTH(getdate(),+1))
    EOMONTH(getdate(),+2)

  • tibco

    Thank you Anna,about EOMONTH()

  • Pedro Rodrigues

    I'm a bit puzzled to why this works:

    DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

    In other words, what, in 'DATEDIFF(mm, 0, GETDATE()) - 1', does -1 supposed to mean? Minus 1 day, minu 1 month, minus one what?
    Isn't this assuming some weight to that -1, if so, this is dommed to fail.

    What am I missing?

    • Pedro Rodrigues

      Guess I figured it, just realized I was misunderstanding the return value from DATEDIFF, which is actually an integer rather than a datatime.

Leave a Reply

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">