SQL SERVER – Script to Find First Day of Current Month

Earlier I wrote a blog post about SQL SERVER – Query to Find First and Last Day of Current Month and it is a very popular post. In this post, I convert the datetime to Varchar and later on use it. However, SQL Expert Michael Usov has made a good point suggesting that it is not always a good idea to convert datetime to any other date format as it is quite possible that we may need it the value in the datetime format for other operation. He has suggested a very quick solution where we can get the first day of the current month with or without time value and keep them with datatype datetime.

Here is the simple script for the same.

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

Here is the resultset:

SQL SERVER - Script to Find First Day of Current Month firstday

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

SQL DateTime, SQL Function
Previous Post
SQL SERVER – ​Finding Out What Changed in a Deleted Database – Notes from the Field #041
Next Post
SQL SERVER – Validating If Date is Last Day of the Year, Month or Day

Related Posts

15 Comments. Leave new

  • select dateadd(month,-1,dateadd(day,1,eomonth(getdate())))

    Reply
    • Instead of using dateadd function twice, use it once by following way
      SELECT DATEADD(DAY,1,EOMONTH(GETDATE(),-1))

      Reply
  • I also want to give one more solution. let me know the feedback for my post.

    Reply
  • I also want to give one more solution. let me know the feedback for my post.

    ——————————————————————————————————–
    —Get First day of Current Month
    SELECT CAST(CONVERT(VARCHAR(20), YEAR(GETDATE())) + ‘-‘ + CONVERT(VARCHAR(20), MONTH(GETDATE())) + ‘-‘ + CONVERT(VARCHAR(20), 1) AS DATETIME)

    —Get Last day of Current Month
    SELECT DATEADD(DAY, -1, CAST(CONVERT(VARCHAR(20), YEAR(GETDATE())) + ‘-‘ + CONVERT(VARCHAR(20), MONTH(GETDATE()) + 1) + ‘-‘ + CONVERT(VARCHAR(20), 1) AS DATETIME))

    Reply
  • Thanks for reply

    Reply
  • James Anderson
    August 19, 2014 7:31 pm

    This is the best I have seen

    SELECT DATEADD(D,1, EOMONTH(GETDATE(), -1))

    SQL 2012/14 only though

    Reply
    • I have tried the above query in SQL SERVER 2012. I have received the below error,

      Msg 195, Level 15, State 10, Line 1
      ‘EOMONTH’ is not a recognized built-in function name.

      Any suggestion….

      Reply
      • James Anderson
        August 26, 2014 1:49 pm

        What is the compatibility level of the database you are trying to run the query on?

        This SQL will tell you

        SELECT compatibility_level
        FROM sys.databases WHERE name = ‘MyDatabaseName’;
        GO

        The SQL 2012 compatibility level is 110. If your level is lower you need to upgrade the database.

      • Udaya Kumar Chowdary
        October 16, 2014 3:48 pm

        my db is not showing any data….its showing only the header with ‘compatabilitylevel’…so what it means?

      • James Anderson
        October 17, 2014 1:21 pm

        Have you changed ‘MyDatabaseName’ to the name of a database in the instance you are connected to?

  • Using any characters at all is going to slow it down tremendously. Thus, stick to straight date math:
    SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS first_day_of_month, DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) AS last_day_of_month

    Reply
  • Swati Singhal
    June 11, 2015 12:57 pm

    Another simple one (only for Sql 2012 & 2014),

    Select DATEFROMPARTS(DATEPART(YYYY,GETDATE()),DATEPART(MM,GETDATE()),1)

    Reply
  • how about this: select cast(convert(varchar(6),getdate(),112) + ’01’ as datetime)

    Reply
  • will work in any database which has only day function
    declare @d datetime
    set @d = ‘2020/10/05’
    select @d – day( @d ) + 35 – day(@d -day( @d ) + 35) as [last day of month],
    @d – day( @d ) +1 as [first day of month]

    Reply

Leave a Reply