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:

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – Script to Find First Day of Current Month

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

    • 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….

      • 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.

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