SQL SERVER – Query to Find First and Last Day of Current Month – Date Function

SQL SERVER - Query to Find First and Last Day of Current Month - Date Function bigclock-800x800 Following query will run respective to today’s date. It will return Last Day of Previous Month, First Day of Current Month, Today, Last Day of Previous Month and First Day of Next Month respective to current month. Let us see how we can do this with the help of Date Function in SQL Server.

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101),
'Last Day of Previous Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,
'First Day of Current Month' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),
DATEADD(mm,1,@mydate)),101),
'Last Day of Current Month'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),
DATEADD(mm,1,@mydate)),101),
'First Day of Next Month'
GO

Here are few additional blog posts on this subject:

I have seen scores of expert developers getting perplexed with SQL Server in finding time only from datetime datatype. Let us have a quick glance look at the solution. Let us learn about how to get Time in Hour:Minute Format from a Datetime as well as to get Date Part Only from Datetime.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – UDF – Function to Parse AlphaNumeric Characters from String
Next Post
SQL SERVER – MS TechNet : Storage Top 10 Best Practices

Related Posts

188 Comments. Leave new

  • If SQL Server was a country, you’d be on that country’s Mt. Rushmore. If SQL Server was a NFL team, you’d be the franchise player. Who’s the man? Pinal Dave is the man.

    Reply
  • Awesome….i found each n every solution of my SQL question from your blog..salute to you…

    Reply
  • I need to run a report on monthly basis.
    My requirement is when ever i run the report ,the query should take first and last date of previous month using sysdate.Can Some one help me on this

    Reply
  • Hi Pinal ,

    I think its good solutions but can I suggest not converting to varchar as depending on what people do with the code after it could be wrong. ie there using an Australian Date format vs US date format etc

    I modded the solution for the first day of the month

    –first day of month
    –with time zeroed out
    SELECT CAST(DATEADD(DAY,-DAY(getdate())+1, CAST(GETDATE() as date)) as datetime)
    –with time as it was
    SELECT DATEADD(DAY,-DAY(getdate())+1, CAST(GETDATE() as datetime))

    Would just need to replace getdate() with the whatever date they want to pass in.

    P.S. love your blog. Its been really helpful

    Reply
  • Thanks for sharing knowledge

    Reply
  • Thanks Dave

    Reply
  • Thanks. Past few months I was using this code, but today..

    I tried to get the first day of the 4th month, where current day is in October. Instead of giving me ‘2/1/2014’, it gave me ‘1/30/2014’.

    Reply
  • I meant the year ‘2015’

    Reply
  • sir can u explain easily way please

    Reply
  • Thank you!

    Reply
  • I would use following method to avoid any typecasting

    SELECT DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0) AS FirstOfTheMonth, DateAdd(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, getdate()) + 1 , 0) AS LastDateOfMonth

    Reply
  • For Latest SQL server SELECT EOMONTH(GETDATE()) will return last day of month.

    Reply
    • EOMONTH was not there in earlier versions of SQL.

      Reply
    • Howard Rothenburg
      April 20, 2016 8:53 pm

      SELECT DATEDIFF(yy, BirthDate, GETDATE()) – CASE
      WHEN MONTH(BirthDate) > MONTH(GETDATE())
      OR (MONTH(BirthDate) = MONTH(GETDATE())
      AND DAY(BirthDate) > DAY(GETDATE()))
      THEN 1
      ELSE 0
      END AS Age;

      Reply
      • Howard Rothenburg
        April 20, 2016 8:54 pm

        DECLARE @PreviousMonthStart DATETIME
        DECLARE @PreviousMonthEnd DATETIME

        SET @PreviousMonthStart = DATEADD(m,DATEDIFF(m,0,GETDATE())-1,0)
        SET @PreviousMonthEnd = DATEADD(ms,-2,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0))

        PRINT @PreviousMonthStart
        PRINT @PreviousMonthEnd

        SELECT * FROM MyTable
        WHERE MyDate >= @PreviousMonthStart
        AND MyDate < @PreviousMonthEnd

  • Experts,
    I am not sure if this blog page is active, but I have a scenario I need help with.

    I want to populate 1 year at a time the First_date_of _Month and Last_date_of_Month into a table.

    Something like this:

    Year | First_Date_of_Month|Last_date_of_Month
    2013|01-01-2013|01-31-2013
    2013|02-01-2013|02-28-2013 and so on…Help!

    Reply
  • need 2016-02-02 .this format .how can i do it using abouv query

    Reply
  • Great, small change in the script. Don’t required to add a month for getting a day for “Last Day of Current Month” and “First Day of Next Month”.

    Modified script below:

    DECLARE @mydate DATETIME
    SELECT @mydate = GETDATE()
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101),’Last Day of Previous Month’
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101) AS Date_Value,’First Day of Current Month’ AS Date_Type
    UNION
    SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, ‘Today’ AS Date_Type
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),DATEADD(mm,1,@mydate)),101),’Last Day of Current Month’
    UNION
    SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),DATEADD(mm,1,@mydate)),101),’First Day of Next Month’
    GO

    Reply

Leave a Reply

Menu