SQL SERVER – Simple Method to Find FIRST and LAST Day of Current Date

In my very old post SQL SERVER – Query to Find First and Last Day of Current Month – Date Function, I had explained how to find the first day and last day of the current date.

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.

SQL SERVER – Script/Function to Find Last Day of Month
SQL SERVER – Validating If Date is Last Day of the Year, Month or Day

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)