The method I showed you in the old posted uses CONVERT function as well DATEADD functions
There is a simple way to the same
Let us consider the following code
DECLARE @DATE DATETIME SET @DATE='2017-10-28' SELECT @DATE AS GIVEN_DATE, @DATE-DAY(@DATE)+1 AS FIRST_DAY_OF_DATE, EOMONTH(@DATE) AS LAST_DAY_OF_MONTH
There result is shown below
The logic is very simple.
The first part @DATE-DAY(@DATE) results to the Last day of a previous month and adding 1 to it will result on the first day of current month.
The second part EOMONTH(@DATE) makes use of SYSTEM function EOMONTH which results to the last day of the given date.
Note that the system function EOMONTH was introduced in version 2012. For older versions of SQL Server refer the following relevant posts for alternate methods.
Let me know if you know any other better way to get the first and last day of the current date. I will be happy to post your script with due credit to you.
Reference: Pinal Dave (https://blog.sqlauthority.com)